Loading Parquet data into MySQL with ClickHouse

A couple of weeks ago, I was working on a video for the MySQL CDC connector for ClickPipes and I needed to get some data from Parquet files into MySQL. There isn't a native way to do this, so I decided to use clickhouse-local, my favorite tool for doing this type of adhoc data work.

Setting up MySQL

I used AWS RDS for MySQL in the video, but to make it easier to reproduce, we'll use MySQL running on my machine for this blog post. We can launch MySQL by running the following:

1docker run --name parquet-mysql \
2    -p 127.0.0.1:3306:3306 \
3    -e MYSQL_ROOT_PASSWORD=local-root-password \
4    -e MYSQL_DATABASE=stackoverflow \
5    -e MYSQL_USER=admin \
6    -e MYSQL_PASSWORD=local-demo-password \
7    -v parquet-mysql-data:/var/lib/mysql \
8    mysql:8.4

Setting up ClickHouse

We'll run ClickHouse via its binary, which we can download like this:

1curl https://clickhouse.com | sh

We'll then launch it:

1./clickhouse -mn \
2--config-file ch-config.yaml \
3--param_mysql_password "local-demo-password" \
4--param_mysql_host "127.0.0.1:3306" \
5--output-format Pretty

Tip

We're passing in the MySQL host and password in plain text because it's running on our machine. For a production system, we'd have those values stored in environment variables.

The contents of ch-config.yaml are shown below:

1echo: 0
2users_config: ch-users.yaml
3named_collections:
4  mysql_demo:
5    host: '127.0.0.01'
6    port: 3306
7    user: 'admin'
8    password: 'local-demo-password'
9    database: 'stackoverflow'

ch-users.yaml contains config for the default user. The named collection mysql_demo contains our MySQL credentials that we'll use later in the post.

Exploring StackOverflow dataset

Now that we've got MySQL and ClickHouse running, let's explore our dataset. We're going to work with StackOverflow votes data, which lives in the following S3 bucket:

1SET url_base = 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/votes/';

When using the url table function, the url_base parameter is prepended to relative paths so that we don't have to repeat the same base URL multiple times. We can describe the 2024.parquet file in that bucket like this:

1DESCRIBE url('2024.parquet');

1┌──────────────┬──────────────────────┐
2│ name         │ type                 │
3├──────────────┼──────────────────────┤
4│ Id           │ Int64                │
5│ PostId       │ Int64                │
6│ VoteTypeId   │ Int64                │
7│ CreationDate │ DateTime64(3, 'UTC') │
8│ UserId       │ Int64                │
9│ BountyAmount │ UInt64               │
10└──────────────┴──────────────────────┘
11
126 rows in set. Elapsed: 0.445 sec.

Creating table in MySQL

Let's now connect to MySQL:

1docker exec -it parquet-mysql mysql -u admin -p stackoverflow

And then run the following query to create a table to store this data:

1CREATE TABLE votes_from_parquet
2(
3    Id           BIGINT NOT NULL PRIMARY KEY,
4    PostId       BIGINT,
5    VoteTypeId   BIGINT NOT NULL,
6    CreationDate DATETIME(3) NOT NULL,
7    UserId       BIGINT,
8    BountyAmount BIGINT UNSIGNED DEFAULT 0
9)
10ENGINE = InnoDB;

1Query OK, 0 rows affected (0.039 sec)

Inserting data in MySQL

Back to ClickHouse we go to ingest the data. We're going to use the mysql table function, as shown in the following query:

1INSERT INTO FUNCTION mysql(
2    {mysql_host:String},     -- MySQL host
3    'stackoverflow',         -- MySQL database
4    'votes_from_parquet',    -- MySQL table
5    'admin',                 -- MySQL user
6    {mysql_password:String}  -- MySQL password
7)
8(Id, PostId, VoteTypeId, CreationDate, UserId, BountyAmount)
9SELECT Id, PostId, VoteTypeId, CreationDate, UserId, BountyAmount
10FROM url('2024.parquet');

12296977 rows in set. Elapsed: 16.609 sec. Processed 2.30 million rows, 21.63 MB (138.29 thousand rows/s., 1.30 MB/s.)
2Peak memory usage: 227.31 MiB.

Querying MySQL from ClickHouse

We can also query MySQL by using the mysql table function in the FROM clause:

1SELECT *
2FROM mysql(
3    {mysql_host:String},
4    'stackoverflow',
5    (
6        SELECT count(*) AS `votes`
7        FROM votes_from_parquet
8        WHERE tuple(VoteTypeId, PostId) IN ((2, 37996101), (2, 802038))
9    ),
10    'admin',
11    {mysql_password:String}
12);

When we pass in the inner query like this, it uses ClickHouse syntax. ClickHouse parses it into a syntax tree, converts supported ClickHouse constructs into MySQL-valid SQL, and then sends the resulting query to MySQL for execution.

1┏━━━━━━━┓
2┃ votes ┃
3┡━━━━━━━┩
4│     5 │
5└───────┘
6
71 row in set. Elapsed: 1.708 sec.

Alternatively, we can pass in the query as a string to the query function, but this time the query must be MySQL-SQL. ClickHouse treats it as an opaque string and sends it directly to MySQL without parsing or rewriting the SQL inside it. If we pass it as is, we'll get an exception:

1SELECT *
2FROM mysql(
3    {mysql_host:String},
4    'stackoverflow',
5    query($sql$
6        SELECT count(*) AS `votes`
7        FROM votes_from_parquet
8        WHERE tuple(VoteTypeId, PostId) IN ((2, 37996101), (2, 802038))
9    $sql$),
10    'admin',
11    {mysql_password:String}
12);

1Received exception:
2Code: 1000. DB::Exception: mysqlxx::BadQuery: FUNCTION stackoverflow.tuple does not exist while executing query: 'SELECT * FROM (
3        SELECT count(*) AS `votes`
4        FROM votes_from_parquet
5        WHERE tuple(VoteTypeId, PostId) IN ((2, 37996101), (2, 802038))
6    ) AS __subquery LIMIT 0' (127.0.0.1:3306). (POCO_EXCEPTION)

tuple doesn't exist in MySQL, so its unable to parse the query. But if we remove tuple and send this query, it'll work fine:

1SELECT *
2FROM mysql(
3    {mysql_host:String},
4    'stackoverflow',
5    query($sql$
6        SELECT count(*) AS `votes`
7        FROM votes_from_parquet
8        WHERE (VoteTypeId, PostId) IN ((2, 37996101), (2, 802038))
9    $sql$),
10    'admin',
11    {mysql_password:String}
12);

Querying MySQL from ClickHouse with named collection

We can also query MySQL using the credentials that we defined in a named collection in our config file. We can query the server's named collections with this query:

1SELECT *
2FROM system.named_collections
3FORMAT Vertical;

1Row 1:
2──────
3name:         mysql_demo
4collection:   {'database':'[HIDDEN]','host':'[HIDDEN]','password':'[HIDDEN]','port':'[HIDDEN]','user':'[HIDDEN]'}
5source:       CONFIG
6create_query:
7
81 row in set. Elapsed: 0.001 sec.

We can then pass in mysql_demo as the first argument, with the query being provided as a string:

1SELECT *
2FROM mysql(
3    mysql_demo,
4    query=$sql$
5    SELECT count(*)
6    FROM votes_from_parquet
7    WHERE (VoteTypeId = 2 AND CreationDate = '2024-01-01')
8    $sql$
9);

1┌──────────┐
2│ count(*) │
3├──────────┤
4│ 10067    │
5└──────────┘
6
71 row in set. Elapsed: 1.100 sec.

Let's finish with one more query that finds the posts with both upvotes and downvotes:

1SELECT * FROM mysql(mysql_demo, query = '
2  SELECT PostId, upvotes, downvotes,
3         upvotes + downvotes AS total_votes,
4         ROUND(
5           100.0 * LEAST(upvotes, downvotes) / (upvotes + downvotes),
6           1
7         ) AS balance_pct
8        FROM
9        (
10          SELECT PostId, SUM(VoteTypeId = 2) AS upvotes, 
11                 SUM(VoteTypeId = 3) AS downvotes
12          FROM votes_from_parquet
13          WHERE VoteTypeId IN (2, 3)
14          GROUP BY PostId
15        ) AS vote_totals
16  WHERE upvotes > 0 AND downvotes > 0
17  ORDER BY LEAST(upvotes, downvotes) DESC, total_votes DESC
18  LIMIT 10'
19);

1┌──────────┬─────────┬───────────┬─────────────┬─────────────┐
2│ PostId   │ upvotes │ downvotes │ total_votes │ balance_pct │
3├──────────┼─────────┼───────────┼─────────────┼─────────────┤
4│ 69699772 │ 205     │ 23        │ 228         │ 10.1        │
5│ 69713899 │ 53      │ 9         │ 62          │ 14.5        │
6│ 62099904 │ 37      │ 8         │ 45          │ 17.8        │
7│ 10032024 │ 7       │ 16        │ 23          │ 30.4        │
8│ 78114790 │ 7       │ 12        │ 19          │ 36.8        │
9│ 62477194 │ 7       │ 9         │ 16          │ 43.8        │
10│ 72231704 │ 8       │ 7         │ 15          │ 46.7        │
11│ 77766725 │ 7       │ 8         │ 15          │ 46.7        │
12│ 78129981 │ 24      │ 6         │ 30          │ 20          │
13│ 77767692 │ 6       │ 8         │ 14          │ 42.9        │
14└──────────┴─────────┴───────────┴─────────────┴─────────────┘
15
1610 rows in set. Elapsed: 8.883 sec.

Conclusion

In this blog post, we've used ClickHouse to read a Parquet file, loads its contents into MySQL and query the results.

Being able to do all of that without leaving ClickHouse is why I like table functions so much. And MySQL is just one option. There are also table functions for Postgres, SQLite, and MongoDB, to name just a few.

Get started today

Interested in seeing how ClickHouse works on your data? Get started with ClickHouse Cloud in minutes and receive $300 in free credits.

Loading Parquet data into MySQL with ClickHouse

Setting up MySQL

Setting up ClickHouse

Exploring StackOverflow dataset

Creating table in MySQL

Inserting data in MySQL

Querying MySQL from ClickHouse

Querying MySQL from ClickHouse with named collection

Conclusion

Get started today

Subscribe to our newsletter

Recent posts

AI Functions in ClickHouse: Upgrade your SQL to the AI age

ClickHouse release 26.8

ClickHouse Cloud vs. Snowflake: What drives the real-time performance-per-dollar gap

Introducing WalShadow: Sub-second Postgres replication to ClickHouse from physical WAL

Follow us

Loading Parquet data into MySQL with ClickHouse

Setting up MySQL #

Setting up ClickHouse #

Exploring StackOverflow dataset #

Creating table in MySQL #

Inserting data in MySQL #

Querying MySQL from ClickHouse #

Querying MySQL from ClickHouse with named collection #

Conclusion #

Get started today

Subscribe to our newsletter

Recent posts

AI Functions in ClickHouse: Upgrade your SQL to the AI age

ClickHouse release 26.8

ClickHouse Cloud vs. Snowflake: What drives the real-time performance-per-dollar gap

Introducing WalShadow: Sub-second Postgres replication to ClickHouse from physical WAL

Follow us

Setting up MySQL

Setting up ClickHouse

Exploring StackOverflow dataset

Creating table in MySQL

Inserting data in MySQL

Querying MySQL from ClickHouse

Querying MySQL from ClickHouse with named collection

Conclusion