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.

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 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 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 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 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 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 to BigQuery

SAP HANA Type	BigQuery Type
TINYINT, SMALLINT, INTEGER, BIGINT	INT64
DECIMAL(p,s), SMALLDECIMAL	NUMERIC
REAL, DOUBLE	FLOAT64
BOOLEAN	BOOL
VARCHAR(n), NVARCHAR(n), CHAR(n), NCHAR(n), CLOB, NCLOB, TEXT	STRING
DATE	DATE
TIME	TIME
TIMESTAMP, SECONDDATE	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})
--publish_table_pattern PATTERN	String	Dynamic table naming pattern (default: {table})
--publish_method TYPE	String	Table type: external (default) or internal
--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:

lakexpress -a credentials.json \
    --lxdb_auth_id lxdb \
    --source_db_auth_id postgres_prod \
    --source_schema_name public \
    --target_storage_id gcs_datalake \
    --fastbcp_dir_path /path/to/FastBCP \
    --publish_target bigquery_prod \
    --tables customer \
    --dry_run

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