BigQuery Reference

Reference material for BigQuery Publishing -- data type mappings, CLI arguments, querying, and troubleshooting.

Data Type Mapping

Source types map automatically to BigQuery-compatible types. LakeXpress exports through Parquet, so the mapping is driven by the normalized source type name.

Pick your source database:

PostgreSQL

PostgreSQL to BigQuery

PostgreSQL Type	BigQuery Type
SMALLINT, INTEGER, INT4, BIGINT, INT8	INT64
NUMERIC(p,s)	NUMERIC
REAL, FLOAT4, DOUBLE PRECISION, FLOAT8	FLOAT64
VARCHAR(n), TEXT, CHAR(n), UUID, array []	STRING
BOOLEAN	BOOL
DATE	DATE
TIME	TIME
TIMESTAMP, TIMESTAMPTZ	TIMESTAMP
BYTEA	BYTES
JSON, JSONB	JSON

SQL Server

SQL Server to BigQuery

SQL Server Type	BigQuery Type
TINYINT, SMALLINT, INT, BIGINT	INT64
DECIMAL(p,s), MONEY, SMALLMONEY	NUMERIC
FLOAT, REAL	FLOAT64
BIT	BOOL
VARCHAR(n), NVARCHAR(n), CHAR(n), NCHAR(n), TEXT, NTEXT, UNIQUEIDENTIFIER	STRING
DATE	DATE
DATETIME, DATETIME2, SMALLDATETIME	DATETIME
DATETIMEOFFSET	TIMESTAMP
VARBINARY	BYTES

Oracle

Oracle to BigQuery

Oracle Type	BigQuery Type
NUMBER, NUMBER(p,s)	NUMERIC
FLOAT	FLOAT64
VARCHAR2(n), NVARCHAR2(n), CHAR(n), CLOB, NCLOB	STRING
DATE	DATE
TIMESTAMP	TIMESTAMP
RAW, BLOB	BYTES

Teradata

Teradata to BigQuery

Teradata Type	BigQuery Type
BYTEINT, VARCHAR(n), CHAR(n), CLOB, VARBYTE	STRING
SMALLINT, INTEGER, BIGINT	INT64
DECIMAL(p,s), NUMERIC(p,s), NUMBER	NUMERIC
FLOAT, REAL	FLOAT64
DATE	DATE
TIME	TIME
TIMESTAMP	TIMESTAMP
BYTE, BLOB	BYTES

MySQL

MySQL to BigQuery

MySQL Type	BigQuery Type
TINYINT, SMALLINT, INT, BIGINT	INT64
MEDIUMINT, YEAR, VARCHAR(n), CHAR(n), TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, ENUM, SET, TINYBLOB, MEDIUMBLOB, LONGBLOB	STRING
DECIMAL(p,s)	NUMERIC
FLOAT, DOUBLE	FLOAT64
BIT	BOOL
DATE	DATE
TIME	TIME
DATETIME	DATETIME
TIMESTAMP	TIMESTAMP
BINARY, VARBINARY, BLOB	BYTES
JSON	JSON

MariaDB

MariaDB to BigQuery

MariaDB Type	BigQuery Type
TINYINT, SMALLINT, INT, BIGINT	INT64
MEDIUMINT, YEAR, VARCHAR(n), CHAR(n), TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, ENUM, SET, TINYBLOB, MEDIUMBLOB, LONGBLOB	STRING
DECIMAL(p,s)	NUMERIC
FLOAT, DOUBLE	FLOAT64
BIT	BOOL
DATE	DATE
TIME	TIME
DATETIME	DATETIME
TIMESTAMP	TIMESTAMP
BINARY, VARBINARY, BLOB	BYTES
JSON	JSON

SAP HANA

SAP HANA to BigQuery

SAP HANA Type	BigQuery Type
TINYINT, SMALLINT, INTEGER, BIGINT	INT64
DECIMAL(p,s)	NUMERIC
SMALLDECIMAL, VARCHAR(n), NVARCHAR(n), CHAR(n), NCHAR(n), CLOB, NCLOB, TEXT, SECONDDATE	STRING
REAL, DOUBLE	FLOAT64
BOOLEAN	BOOL
DATE	DATE
TIME	TIME
TIMESTAMP	TIMESTAMP
VARBINARY, BLOB	BYTES

CLI Reference

BigQuery Publishing Arguments

Option	Type	Description
--publish_target ID	String	Credential ID for BigQuery publishing (required)
--publish_schema_pattern PATTERN	String	Dynamic dataset naming pattern (default: {schema} for internal, EXT_{schema} for the default external method)
--publish_table_pattern PATTERN	String	Dynamic table naming pattern (default: {table})
--publish_method TYPE	String	Table type: external (default) or internal
--pk_constraints	Flag	Add PRIMARY KEY constraints to native (internal) table DDL; not supported for external tables
--n_jobs N	Integer	Parallel workers for table creation (default: 1)

Querying BigQuery Tables

BigQuery Console:

SELECT * FROM `my-project.lx_tpch_1.customer` LIMIT 10;

bq CLI:

bq query --use_legacy_sql=false \
  'SELECT * FROM `my-project.lx_tpch_1.customer` LIMIT 10'

Python (google-cloud-bigquery):

from google.cloud import bigquery

client = bigquery.Client()
query = "SELECT * FROM `my-project.lx_tpch_1.customer` LIMIT 10"
df = client.query(query).to_dataframe()
print(df)

pandas-gbq:

import pandas_gbq

query = "SELECT * FROM `my-project.lx_tpch_1.customer` LIMIT 10"
df = pandas_gbq.read_gbq(query, project_id="my-project")
print(df)

Troubleshooting

Common Issues

"Permission denied" errors:

• Verify the service account has BigQuery Data Editor role
• Check GCS bucket access from the service account
• Ensure project ID in credentials matches the target project

"Dataset not found" errors:

• Check that location matches your GCS bucket region
• Cross-region access between GCS and BigQuery may cause issues

"Invalid table" errors for external tables:

• Verify GCS path contains valid Parquet files
• Check schema mapping for your data types

Verifying Setup

Test BigQuery connectivity by creating a minimal sync configuration against a single schema, then run it and check the log output:

./LakeXpress config create \
    -a credentials.json \
    --lxdb_auth_id lxdb_postgres \
    --source_db_auth_id source_postgres \
    --source_db_name mydb \
    --source_schema_name public \
    --target_storage_id gcs_01 \
    --sub_path mydata \
    --publish_target bigquery_prod \
    --log_level DEBUG

There is no --tables filter or --dry_run flag for BigQuery publishing runs -- to limit scope for a connectivity check, point --source_schema_name at a schema with few tables (see the CLI Reference).

Validate credentials:

from google.cloud import bigquery
from google.oauth2 import service_account

credentials = service_account.Credentials.from_service_account_file(
    '/path/to/service-account.json'
)
client = bigquery.Client(credentials=credentials, project='my-project')
print(list(client.list_datasets()))

See Also

• BigQuery Publishing - Setup and usage guide
• CLI Reference - All command-line options