Getting started with chDB

In this guide, we’re going to get up and running with the Python variant of chDB. We’ll start by querying a JSON file on S3, before creating a table in chDB based on the JSON file, and doing some queries on the data. We’ll also see how to have queries return data in different formats, including Apache Arrow and Panda, and finally we’ll learn how to query Pandas DataFrames.

Setup

Let’s first create a virtual environment:

python -m venv .venv
source .venv/bin/activate

And now we’ll install chDB. Make sure you have version 2.0.3 or higher:

pip install "chdb>=2.0.2"

And now we’re going to install ipython:

pip install ipython

We’re going to use ipython to run the commands in the rest of the guide, which you can launch by running:

ipython

We’ll also be using Pandas and Apache Arrow in this guide, so let’s install those libraries too:

pip install pandas pyarrow

Querying a JSON file in S3

Let’s now have a look at how to query a JSON file that’s stored in an S3 bucket. The YouTube dislikes dataset contains more than 4 billion rows of dislikes on YouTube videos up to 2021. We’re going to work with one of the JSON files from that dataset. Import chdb:

import chdb

We can write the following query to describe the structure of one of the JSON files:

chdb.query(
  """
  DESCRIBE s3(
    's3://clickhouse-public-datasets/youtube/original/files/' ||
    'youtubedislikes_20211127161229_18654868.1637897329_vid.json.zst',
    'JSONLines'
  )
  SETTINGS describe_compact_output=1
  """
)

"id","Nullable(String)"
"fetch_date","Nullable(String)"
"upload_date","Nullable(String)"
"title","Nullable(String)"
"uploader_id","Nullable(String)"
"uploader","Nullable(String)"
"uploader_sub_count","Nullable(Int64)"
"is_age_limit","Nullable(Bool)"
"view_count","Nullable(Int64)"
"like_count","Nullable(Int64)"
"dislike_count","Nullable(Int64)"
"is_crawlable","Nullable(Bool)"
"is_live_content","Nullable(Bool)"
"has_subtitles","Nullable(Bool)"
"is_ads_enabled","Nullable(Bool)"
"is_comments_enabled","Nullable(Bool)"
"description","Nullable(String)"
"rich_metadata","Array(Tuple(
    call Nullable(String),
    content Nullable(String),
    subtitle Nullable(String),
    title Nullable(String),
    url Nullable(String)))"
"super_titles","Array(Tuple(
    text Nullable(String),
    url Nullable(String)))"
"uploader_badges","Nullable(String)"
"video_badges","Nullable(String)"

We can also count the number of rows in that file:

chdb.query(
  """
  SELECT count()
  FROM s3(
    's3://clickhouse-public-datasets/youtube/original/files/' ||
    'youtubedislikes_20211127161229_18654868.1637897329_vid.json.zst',
    'JSONLines'
  )"""
)

This file contains just over 300,000 records. chdb doesn’t yet support passing in query parameters, but we can pull out the path and pass it in via an f-String.

path = 's3://clickhouse-public-datasets/youtube/original/files/youtubedislikes_20211127161229_18654868.1637897329_vid.json.zst'

chdb.query(
  f"""
  SELECT count()
  FROM s3('{path}','JSONLines')
  """
)

This is fine to do with variables defined in your program, but don’t do it with user-provided input, otherwise your query is open to SQL injection.

Configuring the output format

The default output format is CSV, but we can change that via the output_format parameter. chDB supports the ClickHouse data formats, as well as some of its own, including DataFrame, which returns a Pandas DataFrame:

result = chdb.query(
  f"""
  SELECT is_ads_enabled, count()
  FROM s3('{path}','JSONLines')
  GROUP BY ALL
  """,
  output_format="DataFrame"
)

print(type(result))
print(result)

<class 'pandas.core.frame.DataFrame'>
   is_ads_enabled  count()
0           False   301125
1            True    35307

Or if we want to get back an Apache Arrow table:

result = chdb.query(
  f"""
  SELECT is_live_content, count()
  FROM s3('{path}','JSONLines')
  GROUP BY ALL
  """,
  output_format="ArrowTable"
)

print(type(result))
print(result)

<class 'pyarrow.lib.Table'>
pyarrow.Table
is_live_content: bool
count(): uint64 not null
----
is_live_content: [[false,true]]
count(): [[315746,20686]]

Creating a table from JSON file

Next, let’s have a look at how to create a table in chDB. We need to use a different API to do that, so let’s first import that:

from chdb import session as chs

Next, we’ll initialize a session. If we want the session to be persisted to disk, we need to provide a directory name. If we leave it blank, the database will be in-memory and lost as soon as we kill the Python process.

sess = chs.Session("gettingStarted.chdb")

Next, we’ll create a database:

sess.query("CREATE DATABASE IF NOT EXISTS youtube")

Now we can create a dislikes table based on the schema from the JSON file, using the CREATE...EMPTY AS technique. We’ll use the schema_inference_make_columns_nullable setting so that column types aren’t all made Nullable.

sess.query(f"""
  CREATE TABLE youtube.dislikes
  ORDER BY fetch_date 
  EMPTY AS 
  SELECT * 
  FROM s3('{path}','JSONLines')
  SETTINGS schema_inference_make_columns_nullable=0
  """
)

We can then use the DESCRIBE clause to inspect the schema:

sess.query(f"""
   DESCRIBE youtube.dislikes
   SETTINGS describe_compact_output=1
   """
)

"id","String"
"fetch_date","String"
"upload_date","String"
"title","String"
"uploader_id","String"
"uploader","String"
"uploader_sub_count","Int64"
"is_age_limit","Bool"
"view_count","Int64"
"like_count","Int64"
"dislike_count","Int64"
"is_crawlable","Bool"
"is_live_content","Bool"
"has_subtitles","Bool"
"is_ads_enabled","Bool"
"is_comments_enabled","Bool"
"description","String"
"rich_metadata","Array(Tuple(
    call String,
    content String,
    subtitle String,
    title String,
    url String))"
"super_titles","Array(Tuple(
    text String,
    url String))"
"uploader_badges","String"
"video_badges","String"

Next, let’s populate that table:

sess.query(f"""
  INSERT INTO youtube.dislikes
  SELECT * 
  FROM s3('{path}','JSONLines')
  SETTINGS schema_inference_make_columns_nullable=0
  """
)

We could also do both these steps in one go, using the CREATE...AS technique. Let’s create a different table using that technique:

sess.query(f"""
  CREATE TABLE youtube.dislikes2
  ORDER BY fetch_date 
  AS 
  SELECT * 
  FROM s3('{path}','JSONLines')
  SETTINGS schema_inference_make_columns_nullable=0
  """
)

Querying a table

Finally, let’s query the table:

df = sess.query("""
  SELECT uploader, sum(view_count) AS viewCount, sum(like_count) AS likeCount, sum(dislike_count) AS dislikeCount
  FROM youtube.dislikes
  GROUP BY ALL
  ORDER BY viewCount DESC
  LIMIT 10
  """,
  "DataFrame"
)
df

                             uploader  viewCount  likeCount  dislikeCount
                           Jeremih  139066569     812602         37842
                   TheKillersMusic  109313116     529361         11931
LetsGoMartin- Canciones Infantiles  104747788     236615        141467
                  Xiaoying Cuisine   54458335    1031525         37049
                              Adri   47404537     279033         36583
                Diana and Roma IND   43829341     182334        148740
                    ChuChuTV Tamil   39244854     244614        213772
                          Cheez-It   35342270        108            27
                          Anime Uz   33375618    1270673         60013
                  RC Cars OFF Road   31952962     101503         49489

Let’s say we then add an extra column to the DataFrame to compute the ratio of likes to dislikes. We could write the following code:

df["likeDislikeRatio"] = df["likeCount"] / df["dislikeCount"]

Querying a Pandas dataframe

We can then query that DataFrame from chDB:

chdb.query(
  """
  SELECT uploader, likeDislikeRatio
  FROM Python(df)
  """,
  output_format="DataFrame"
)

                             uploader  likeDislikeRatio
                           Jeremih         21.473548
                   TheKillersMusic         44.368536
LetsGoMartin- Canciones Infantiles          1.672581
                  Xiaoying Cuisine         27.842182
                              Adri          7.627395
                Diana and Roma IND          1.225857
                    ChuChuTV Tamil          1.144275
                          Cheez-It          4.000000
                          Anime Uz         21.173296
                  RC Cars OFF Road          2.051021

You can also read more about querying Pandas DataFrames in the Querying Pandas developer guide.

Next steps

Hopefully, this guide has given you a good overview of chDB. To learn more about how to use it, see the following developer guides:

Get started

DataStore

Configuration

Debugging

Setup

Querying a JSON file in S3

Configuring the output format

Creating a table from JSON file

Querying a table

Querying a Pandas dataframe

Next steps

​Setup

​Querying a JSON file in S3

​Configuring the output format

​Creating a table from JSON file

​Querying a table

​Querying a Pandas dataframe

​Next steps

Setup

Querying a JSON file in S3

Configuring the output format

Creating a table from JSON file

Querying a table

Querying a Pandas dataframe

Next steps