--- title: "How to query a mysqldump file without importing" date: "2026-06-15" author: "Al Brown" category: "Engineering" excerpt: "Run SQL against the tables inside a mysqldump .sql file directly with clickhouse-local. No MySQL server, no restore step — the INSERT statements are read as a table." --- # How to query a mysqldump file without importing To query a `mysqldump` file without importing it, use [`clickhouse local`](/resources/engineering/what-is-clickhouse-local). It runs SQL directly on files from the command line, with no server to install. It's part of ClickHouse, so the same query scales to billions of rows when you outgrow your laptop. Install it with [`clickhousectl`](https://clickhouse.com/docs/install): ```bash curl https://clickhouse.com/cli | sh # install clickhousectl clickhousectl local use latest # download ClickHouse and put it on your PATH ``` Then point it at the `.sql` file with the `MySQLDump` format and name the table you want: ```bash clickhouse local -q " SELECT * FROM file('shop.sql', MySQLDump) SETTINGS input_format_mysql_dump_table_name = 'orders' FORMAT PrettyCompact" ``` ```response ┌─id─┬─customer_id─┬─product─┬─revenue─┬─quantity─┐ 1. │ 1 │ 1 │ widget │ 5 │ 1 │ 2. │ 2 │ 2 │ gadget │ 6.01 │ 2 │ 3. │ 3 │ 3 │ gizmo │ 7.02 │ 3 │ 4. │ 4 │ 1 │ widget │ 8.03 │ 4 │ └────┴─────────────┴─────────┴─────────┴──────────┘ ``` The `MySQLDump` format reads the `INSERT` statements straight out of the dump and exposes them as a table, so the file is queried in place with no restore step first. ## A dump usually has several tables — list them first A typical `mysqldump` file holds every table in a database, one `CREATE TABLE` plus one `INSERT INTO` per table. Before you query, it helps to see what's in there. The table names live on the `INSERT INTO` lines, so read the file as raw lines and pull them out: ```bash clickhouse local -q " SELECT extract(line, 'INSERT INTO .(\w+).') AS table_name FROM file('shop.sql', LineAsString) WHERE line LIKE 'INSERT INTO%'" ``` ```response customers orders ``` That tells you which names you can pass to `input_format_mysql_dump_table_name` below. ## See the schema without a CREATE TABLE `mysqldump` writes the `CREATE TABLE` DDL right above the data, and `clickhouse local` reads it. `DESCRIBE` shows the column names and the types it mapped from the MySQL definitions, with no schema to declare yourself: ```bash clickhouse local -q " DESCRIBE file('shop.sql', MySQLDump) SETTINGS input_format_mysql_dump_table_name = 'orders' FORMAT PrettyCompact" ``` ```response ┌─name────────┬─type─────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐ 1. │ id │ Int32 │ │ │ │ │ │ 2. │ customer_id │ Nullable(Int32) │ │ │ │ │ │ 3. │ product │ Nullable(String) │ │ │ │ │ │ 4. │ revenue │ Nullable(Decimal(10, 2)) │ │ │ │ │ │ 5. │ quantity │ Nullable(Int32) │ │ │ │ │ │ └─────────────┴──────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘ ``` The MySQL `int NOT NULL` primary key became `Int32`, the nullable `int` columns became `Nullable(Int32)`, and `decimal(10,2)` was preserved as `Decimal(10, 2)`. Reading the rows is then a plain `SELECT`: ```bash clickhouse local -q " SELECT * FROM file('shop.sql', MySQLDump) SETTINGS input_format_mysql_dump_table_name = 'orders' FORMAT PrettyCompact" ``` ```response ┌─id─┬─customer_id─┬─product─┬─revenue─┬─quantity─┐ 1. │ 1 │ 1 │ widget │ 5 │ 1 │ 2. │ 2 │ 2 │ gadget │ 6.01 │ 2 │ 3. │ 3 │ 3 │ gizmo │ 7.02 │ 3 │ 4. │ 4 │ 1 │ widget │ 8.03 │ 4 │ └────┴─────────────┴─────────┴─────────┴──────────┘ ``` ## Picking a table — the one setting that matters `input_format_mysql_dump_table_name` is the whole trick. The dump may contain twenty tables; this setting tells `clickhouse local` which one's `INSERT` rows to return. Switch the value to read a different table out of the same file: ```bash clickhouse local -q " SELECT * FROM file('shop.sql', MySQLDump) SETTINGS input_format_mysql_dump_table_name = 'customers'" ``` ```response 1 GB 2026-01-01 2 US 2026-01-02 3 DE 2026-01-03 ``` Leave the setting off and `clickhouse local` reads the **first** table it finds in the file. That's a handy default for single-table dumps (the kind `mysqldump db t` produces), but with a multi-table dump you almost always want to be explicit: ```bash clickhouse local -q "SELECT count() FROM file('shop.sql', MySQLDump)" ``` ```response 3 ``` The setting applies to the whole query, so you read one table per query. To combine tables, query each into a Parquet or Native file once and join those, or load the dump into a ClickHouse server. ## Aggregate straight on the dump Because the dump is just a SQL source, the full ClickHouse dialect works on it — `WHERE`, `GROUP BY`, aggregates, window functions — with nothing loaded in between: ```bash clickhouse local -q " SELECT product, count() AS orders, round(sum(revenue), 2) AS revenue FROM file('shop.sql', MySQLDump) GROUP BY product ORDER BY revenue DESC SETTINGS input_format_mysql_dump_table_name = 'orders' FORMAT PrettyCompact" ``` ```response ┌─product─┬─orders─┬─revenue─┐ 1. │ widget │ 2 │ 13.03 │ 2. │ gizmo │ 1 │ 7.02 │ 3. │ gadget │ 1 │ 6.01 │ └─────────┴────────┴─────────┘ ``` This is the everyday win: a colleague hands you a `.sql` dump, you answer a question about it in one command instead of restoring it into a database first. A `.sql.gz` dump works the same way. `clickhouse local` detects the `.gz` extension and decompresses on the fly, so you never unzip anything ([querying compressed files](/resources/engineering/query-compressed-file)). ## How fast is it? The dump is plain text, so reading it means parsing SQL on every run, with no pre-built table behind it. Even so, parsing is the only cost. On a single-table dump of 2,000,000 rows (`events_large.sql`, ~64 MB of `INSERT` text generated by the example folder below), a full `GROUP BY country` with `sum` and `avg` over every row runs in: ```bash clickhouse local -q " SELECT country, count() AS events, round(sum(revenue), 2) AS revenue, round(avg(quantity), 3) AS avg_qty FROM file('events_large.sql', MySQLDump) GROUP BY country ORDER BY revenue DESC LIMIT 5 SETTINGS input_format_mysql_dump_table_name = 'events' FORMAT PrettyCompact" ``` ```response ┌─country─┬─events─┬─────revenue─┬─avg_qty─┐ 1. │ IN │ 200674 │ 50256630.75 │ 2.997 │ 2. │ CA │ 200217 │ 50087310.66 │ 2.996 │ 3. │ BR │ 200349 │ 50075129.05 │ 2.997 │ 4. │ NL │ 200017 │ 50053869.17 │ 2.999 │ 5. │ AU │ 200001 │ 50030477.09 │ 2.999 │ └─────────┴────────┴─────────────┴─────────┘ ``` **~0.35 seconds**, best of three with a warm OS page cache, on an Apple M4 Pro laptop (14 cores, 24 GB RAM; clickhouse local 26.6.1.117). That includes parsing the whole dump from text on every run. If you'll query the data more than once, convert the table to Parquet once with `INTO OUTFILE 'events.parquet' FORMAT Parquet` and read that instead. Parquet is columnar and typed, so repeat scans get faster again. See [how to query a Parquet file](/resources/engineering/how-to-query-parquet-file). ## The same SQL scales unchanged The `SELECT ... GROUP BY` you ran on a dump file is the same SQL you'd run on a ClickHouse server, or in [ClickHouse Cloud](https://clickhouse.com/cloud). When the data outgrows a laptop, you swap `file('shop.sql', MySQLDump)` for a table name and the rest of the query stays put. You prototype against the dump on your machine and ship the identical logic to production. ## Run it yourself The complete, runnable example lives here. It has `generate.sh` (builds a two-table `shop.sql` dump and the 2M-row `events_large.sql` perf dump, both in real `mysqldump` layout), `run.sh` (every command above), and `expected_output.txt`: **[github.com/ClickHouse/examples → local-analytics/clickhouse-local-mysqldump](https://github.com/ClickHouse/examples/tree/main/local-analytics/clickhouse-local-mysqldump)** ```bash git clone https://github.com/ClickHouse/examples cd examples/local-analytics/clickhouse-local-mysqldump ./generate.sh && ./run.sh ``` ## Related - [What is clickhouse-local?](/resources/engineering/what-is-clickhouse-local) - [How to run SQL on a CSV file](/resources/engineering/run-sql-on-csv-file) - [How to query a Parquet file](/resources/engineering/how-to-query-parquet-file) - [Query a compressed file](/resources/engineering/query-compressed-file)