Basic knowledge on DuckDB

-

DuckDB is an open-source, in-process analytical database management system (OLAP) designed for fast analytical queries. It's often described as "SQLite for analytics" and was first released in 2019 by the Database Architectures group at CWI (Centrum Wiskunde & Informatica) in the Netherlands.

DuckDB is a powerful and high-performance in-process database for fast analytical processing. It is useful for data science and engineering-based applications and provides a full-featured database without the deployment of a separate dedicated database server.

It can interface with multiple programming languages and scripts including SQL, Python, R, Java, and Node.js.










Pic Credit: Google Images

Key Features:

  • In-process database: Runs embedded within your application (no separate server process)
  • Column-oriented storage: Optimized for analytical queries that scan large amounts of data
  • Zero dependencies: Single file deployment, no external dependencies
  • Rich SQL support: PostgreSQL-compatible SQL dialect with advanced analytical functions
  • Parallel execution: Automatic query parallelization across multiple CPU cores
  • Native integrations: Direct querying of Parquet, CSV, JSON files without importing
Pros:
✅ Exceptional analytical performance: 10-100x faster than SQLite for analytical queries
✅ Easy deployment: No server setup, runs in-process like SQLite
✅ Memory efficient: Processes datasets larger than RAM through intelligent buffering
✅ Developer-friendly: Simple API, works with Python, R, Java, Node.js, and more
✅ Query external files directly: Read Parquet, CSV files without loading into database

Cons:
❌ Not for high-concurrency writes: Designed for read-heavy analytical workloads, not transactional applications
❌ Memory usage: Can consume significant RAM for large analytical queries
❌ Not ideal for OLTP: Poor choice for applications with frequent small updates/inserts

When to Choose DuckDB?
  1. Data analysis and exploration
  2. ETL/data pipeline processing
  3. Business intelligence queries
  4. Working with Parquet/CSV files
  5. Scientific computing
  6. When queries aggregate/scan large portions of data
How to Run DuckDB?
DuckDB can be run in several ways depending on your use case. Here are the main methods:

Windows:
Download the CLI from Click Here 

Simple exercise using CLI:



















Python Integration:
pip install duckdb

Simple programs to run using duckdb

Create table and insert data in memory and the data is lost when the prog ends: 



















Using hard-coded data in the program:




































Using CSV file data:


































Using the Parquet file data:

















Using the duckdb file to read the tables and the data in it:

























Popular posts from this blog

Connecting Claude to Pega Infinity 25.1.3 via MCP — Step-by-Step

-
Image
Enable AI-powered access to your Pega Investigative Case Management data directly from Claude prompts using the Model Context Protocol (MCP) and OAuth 2.0. What is MCP and why connect Pega to Claude? The Model Context Protocol (MCP) is an open standard that lets AI assistants like Claude securely call tools and read data from external systems — all from a natural-language prompt. Think of it as a universal adapter between your enterprise systems and AI. Pega Infinity V25.1.3 ships with a first-class MCP service built on its existing REST framework. Once enabled, Claude can: Query and summarize investigative cases without switching apps Retrieve assignment details and case status on demand Trigger Pega workflows from conversational prompts Version requirement: This guide is based on Pega Infinity V25.1.3 (Community Edition) with the Rule-Service-MCP rule type. Earlier versions may not include native MCP support. Architecture overview The connection uses three layers: Claude as the AI c...
Read more

itextpdf API to generate PDF doc from an image file using Pega PE

-
Image
Probably some of the techies might have an idea on an opensource called " itextpdf " (earlier name is lowagie ) to convert an image file(png,jpeg,jpg,tiff,gif etc.) to a PDF file. It is very simple to use this API in Pega function to build the logic using java code. Moreover, this API comes in one single jar file and we need to import it in Pega before we start using it.  I see the conversion comes good in PDF file but not sure if any limitations of this API in terms of conversion is like formatting, font, images etc. So far so good and may be we can keep this API as one of our solutions while working on the requirements accordingly.  Heard that same API can be used to convert the text file to PDF but I didn't explore on it. Those who are interested to work on text -> PDF can take a look at the documentation. Don't hesitate to contact me offline for any further discussion in this context :) :) Prerequisites for doing this exercise: 1) itextpdf 5.4.0 jar file 2) Peg...
Read more

Understanding of Hugging Face platform for AI/ML platform

-
Image
Hugging Face is a platform that provides tools and libraries for natural language processing (NLP), machine learning, and artificial intelligence. It is known for its Transformers   library, which offers pre-trained models for tasks such as text classification, translation, question answering, and more. Hugging Face also hosts a model hub where users can share and discover pre-trained models. Hugging Face is useful in various domains, including: Natural language processing (NLP): Provides tools and pre-trained models for tasks like text classification, translation, sentiment analysis, and question answering. Machine learning research: Offers state-of-the-art models and datasets for researchers to experiment and innovate. Data science and analytics: Helps data scientists analyze and interpret large volumes of text data. AI application development: Enables developers to build AI-powered applications with ease using pre-trained models and APIs. A very simple exercise using the google...
Read more