title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/chdb/getting-started.md)

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[​](#setup "Direct link to 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](https://ipython.org/):

```
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[​](#querying-a-json-file-in-s3 "Direct link to 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](/docs/getting-started/example-datasets/youtube-dislikes) 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'
  )"""
)
```

```
336432
```

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')
  """
)
```

Note

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[​](#configuring-the-output-format "Direct link to 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](/docs/chdb/reference/data-formats), 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[​](#creating-a-table-from-json-file "Direct link to 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`](/docs/operations/settings/formats#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[​](#querying-a-table "Direct link to 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
```