Convert a MySQL schema to BigQuery

Paste one or more MySQL CREATE TABLE statements and get the matching BigQuery table definition, a JSON schema for the bq CLI, and a note on every type that changes.

MySQL CREATE TABLE

Paste the output of SHOW CREATE TABLE your_table; or a mysqldump schema. Several tables at once are fine. Nothing leaves your browser.

BigQuery dataset

CREATE TABLE `analytics.orders` (
  id NUMERIC NOT NULL,
  customer_id INT64 NOT NULL,
  status STRING NOT NULL,
  total NUMERIC NOT NULL,
  is_gift INT64 NOT NULL,
  notes STRING,
  metadata JSON,
  created_at DATETIME NOT NULL,
  updated_at TIMESTAMP,
  PRIMARY KEY (id) NOT ENFORCED
)
CLUSTER BY id;

Column	MySQL	BigQuery
id	bigint unsigned	NUMERICUnsigned BIGINT can exceed INT64's maximum (2^63 − 1), so it becomes NUMERIC.
customer_id	int	INT64
status	enum('pending','paid','shipped','cancelled')	STRINGStored as the value's text.
total	decimal(12,2)	NUMERIC
is_gift	tinyint(1)	INT64MySQL BOOLEAN is TINYINT(1); values stay 0 and 1.
notes	text	STRING
metadata	json	JSON
created_at	datetime	DATETIMENo time zone, like MySQL DATETIME.
updated_at	timestamp	TIMESTAMPAn absolute point in time, shown in UTC.

How MySQL types map to BigQuery

Integers (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, YEAR, BIT) become INT64. BIGINT UNSIGNED becomes NUMERIC, because its largest values don't fit in INT64.
DECIMAL(p,s) becomes NUMERIC when it has at most 29 digits before the point and 9 after; otherwise BIGNUMERIC.
FLOAT and DOUBLE become FLOAT64.
DATE, DATETIME and TIMESTAMP keep their names: DATE, DATETIME (no time zone) and TIMESTAMP (a point in time, in UTC).
JSON becomes BigQuery's native JSON type.
CHAR, VARCHAR, TEXT, ENUM, SET and TIME become STRING.
BINARY, VARBINARY, BLOB and spatial types become BYTES.

This is the same mapping Quayen uses when it creates BigQuery tables for replicated MySQL tables.

To run the conversion in scripts or CI, use the open-source command-line version: mysql-to-bigquery-schema on GitHub (MIT license). It reads mysqldump --no-data output from stdin and prints BigQuery DDL, a JSON schema or the mapping table.

Primary keys and clustering

BigQuery accepts a primary key only as NOT ENFORCED: it documents the key and helps the query optimizer, but doesn't reject duplicates. The generated table is clustered by the primary key, which keeps lookups and merges by ID cheap.

Keeping the table in sync

The DDL creates an empty table. To fill it and keep it current with every insert, update and delete, use change data capture: see how MySQL to BigQuery CDC works and the ways to replicate MySQL to BigQuery in real time.

Questions

What BigQuery type should I use for MySQL DATETIME?

DATETIME. Like MySQL's DATETIME it has no time zone. MySQL TIMESTAMP values are absolute points in time, so they map to BigQuery TIMESTAMP.

Why does BIGINT UNSIGNED become NUMERIC?

BIGINT UNSIGNED goes up to 18,446,744,073,709,551,615, but BigQuery's INT64 stops at 9,223,372,036,854,775,807. NUMERIC holds the full range exactly.

What happens to ENUM, SET and TINYINT(1) columns?

ENUM and SET values are stored as their text in a STRING column. TINYINT(1), which MySQL uses for BOOLEAN, stays INT64 with values 0 and 1.

Does BigQuery enforce primary keys?

No. BigQuery primary keys are declared NOT ENFORCED. A replication tool must apply updates and deletes by primary key itself to avoid duplicates.

Is my schema sent to a server?

No. The conversion runs entirely in your browser.

Your MySQL data in BigQuery today

Free during early access. Set it up yourself in about 15 minutes, or we'll do it with you on a call.

Start for free Book a guided setup