pg2bigquery
A CLI tool to convert query from PostgreSQL to BigQuery
Installation
For npm:
npm i -g pg2bigquery
or yarn:
yarn global add pg2bigquery
Usage
Requirements
$ pg2bigquery -d dataset -t tables.json input output
You need these things to use:
- input: input folder that contains PostgresSQL query files
- output: output folder that contains BigQuery query files
- dataset: destination dataset which is used to run BigQuery query
- tables: list of tables of pg database in json file
If you want to know about the files format, go to samples
Tables JSON file
For now, you need to put the list of tables in a json file like this:
["forms", "roles", "steps", "wards", "people", "staffs", "products"]
You can easily get the list of tables in your sql editor by this query:
select table_name from information_schema.tables;
Run pg2bigquery
with --help
option to get more details:
$ pg2bigquery --help
A success run will be:
$ pg2bigquery -d dataset -t tables.json input output
check output folder... done
get input files... 10 files
get tables... 55 tables
convert 10 files... done
Limitations
The converted files might not be perfect.
When you use my tool, you will see these cases:
No matching signature for operator
(two different types)
No matching signature for operator = for argument types: INT64, STRING. Supported signature: ANY = ANY at [382:44]
BigQuery don't implicit cast one type to another when using with operators (+, -, <, >,...) so you have to do it for yourself.