querybara import

querybara import loads a file into a table: CSV, TSV, JSON, JSON Lines (gzip too), Excel (.xlsx), XML or Parquet. It detects the format, encoding, CSV delimiter and header, matches the file’s columns to the table’s by name, and loads the rows in batches. It can create the table from the file, and update, upsert or delete rows by key instead of appending.

Usage

querybara import --help

Usage: querybara import [options] <target>

import a CSV, TSV, JSON, JSON Lines, Excel, XML or Parquet file into a table

Arguments:
  target                    profile name or id, or connection URI

Options:
  --table <name>            table to import into (schema.table on PostgreSQL)
  --file <path>             file to read, gzip allowed ("-" for stdin)
  --format <format>         file format (default: from the name and content)
                            (choices: "csv", "tsv", "json", "jsonl", "xlsx",
                            "xml", "parquet")
  --delimiter <char>        CSV delimiter: one character, or tab, comma,
                            semicolon, pipe (default: detected)
  --no-header               the first row is data, not column names
  --sheet <name>            Excel: the worksheet to read (default: the first
                            visible one)
  --header-row <n>          Excel: the row with the column names, 0 for none
                            (default: detected)
  --row-path <path>         XML: path of the row elements, e.g. /orders/order
                            (default: detected)
  --encoding <name>         text encoding, e.g. windows-1252 (default: detected)
  --null <text>             unquoted text that means NULL (default: an empty
                            field)
  --mode <mode>             what to do with each row (choices: "append",
                            "update", "upsert", "delete", "replace", default:
                            "append")
  --key <columns>           key columns for update, upsert and delete (default:
                            primary key)
  --create                  create the table from the file's columns and
                            inferred types
  --batch-size <n>          rows per batch (default 1000)
  --transaction <mode>      one transaction for the file, or one per batch
                            (choices: "single", "per-batch", default: "single")
  --on-error <action>       stop (and roll back) or skip failing rows (choices:
                            "stop", "skip", default: "stop")
  --map <file=column>       pair a file column with a table column; repeatable
                            (default: match by name)
  --disable-fk-checks       skip foreign key checks during the load (PostgreSQL:
                            session_replication_role, needs superuser)
  --error-log <file>        write every failing row and its error to a file
  --database <name>         database to connect to
  --read-only               refuse to write (the import is refused)
  -y, --yes                 import without asking on production connections and
                            for replace/delete
  --tls <mode>              TLS mode for this run: disable, require, verify-ca
                            or verify-full
  --ssh <user@host[:port]>  reach URI targets through this SSH server; repeat
                            for jump hosts, in order
  --ssh-key <path>          SSH private key file (OpenSSH, PEM or PuTTY .ppk)
  --ssh-password-env <VAR>  take the SSH password from this variable (default
                            QUERYBARA_SSH_PASSWORD, else a prompt)
  --ssh-agent               log in with the keys of ssh-agent (SSH_AUTH_SOCK) or
                            Pageant
  --proxy <url>             reach URI targets (or their first SSH server)
                            through socks5://host:port or http://host:port
  --ssh-accept-new          trust and remember an SSH host key not seen before
                            (a changed key is always refused)
  --known-hosts <path>      SSH known hosts file (default: the desktop app's)
  -h, --help                show help for a command

The format, encoding, CSV delimiter, quote and header, the Excel header row and the XML
row path are detected from the file unless given. Excel cells keep their types (numbers,
booleans, dates as ISO text); XML rows are the elements at --row-path, with their attributes
and child elements as columns. Parquet columns keep the file's types (exact decimals, dates,
timestamps; lists, maps and structs as JSON). Columns are matched to the table's by name (case, spaces, _
and - do not count);
unmatched table columns get their defaults. Rows load in batches of parameterised INSERT
(or UPDATE, upsert, DELETE) statements in one transaction by default: with --on-error stop
the first bad row rolls everything back; with skip, bad rows are reported (row, line,
column, message) and the rest is kept. Ctrl+C cancels and rolls back.

Exit codes: 0 imported, 1 imported but rows were skipped, 2 failed, 130 interrupted.
Safety: read-only targets refuse; production and "confirm writes" profiles, and the
replace (empties the table first) and delete modes, need --yes or a confirmation.

Examples:
  querybara import dev --table public.people --file people.csv
  querybara import dev --table people --file export.json.gz --mode upsert --key id
  querybara import dev --table staging.raw --file data.tsv --create --on-error skip
  querybara import dev --table sales --file q3.xlsx --sheet "July" --header-row 3
  querybara import dev --table orders --file orders.xml --row-path /export/table/row
  querybara import dev --table events --file events.parquet --create
  cat rows.csv | querybara import "mysql://app@db/shop" --table orders --file - --map "Order No=id"

The --tls and SSH options are described in Global options.

Examples

Append a CSV file to a table:

querybara import shop-dev --table shop.customers --file customers.csv

Upsert by key from gzipped JSON:

querybara import shop-dev --table shop.products --file products.json.gz --mode upsert --key id

Create the table from an Excel worksheet:

querybara import shop-dev --table shop.sales --file q3.xlsx --sheet July --create --key id

Read XML rows at a given path:

querybara import shop-dev --table shop.orders --file orders.xml --row-path /export/table/row

Create a table from a Parquet file, with the column types the file declares:

querybara import shop-dev --table shop.order_items_archive --file order_items.parquet --create

Keep going past bad rows and log them:

querybara import shop-dev --table shop.order_items --file items.tsv --on-error skip --error-log rejected.log

Read from stdin and pair a file column with a table column:

cat orders.csv | querybara import "mysql://[email protected]/shop" --table orders --file - --map "Order No=id"

Parquet files

A file is read as Parquet when its name ends in .parquet, .parq or .pq, when it starts with Parquet’s PAR1 marker, or when you pass --format parquet.

• Columns keep the file’s types: decimals stay exact, dates and timestamps keep every digit, and a table created with --create takes its column types from the file’s schema.
• Lists, maps and structs are imported as JSON text.
• Pages compressed with Snappy, GZIP, ZSTD, Brotli or LZ4 are read. ZSTD needs Node.js 22.15 or later. Files compressed with LZO, and encrypted Parquet files, are refused.
• The file is read one row group at a time. A Parquet file or Excel workbook on stdin is copied to a temporary file first, because both are read from their end.

Modes

--mode	What happens to each row
append	Inserted (default)
update	Updates the row with the same key
upsert	Inserts the row, or updates the row with the same key
delete	Deletes the row with the same key
replace	The table is emptied first, then the rows are inserted

The key for update, upsert and delete is the primary key unless you name columns with --key.

Errors and transactions

By default the whole file loads in one transaction. With --on-error stop (the default) the first bad row rolls everything back. With --on-error skip, bad rows are reported with their row, line, column and message, and the rest is kept. --transaction per-batch commits each batch on its own.

Ctrl + C cancels and rolls back.

Safety

• A read-only target refuses the import, and so does --read-only.
• Production and confirm-writes profiles, and the replace and delete modes, need a confirmation or --yes.
• --disable-fk-checks skips foreign key checks during the load; on PostgreSQL it uses session_replication_role, which needs a superuser.

Replace empties the table

--mode replace removes every row of the table before loading the file.

Exit codes

Code	Meaning
0	Imported
1	Imported, but rows were skipped
2	Failed
130	Interrupted

Related

• querybara export
• querybara transfer
• Exit codes