bankyadam / not-so-bigquery

An emulator for the Google BigQuery, that can be run locally, backed by PostgreSQL.

Not-So-BigQuery

An emulator for Google BigQuery, that can be run locally, backed by PostgreSQL.

Compatibility

API Endpoints

More info here.

Functions

More info here.

SQL Compatibility

Since BigQuery uses its own SQL implementation, there can be and there are functionalities that will not work. To achieve most of the features that BigQuery provides, Not-So-BigQuery uses its own query parser to translate to a PostgreSQL-compatible query.

Supported BigQuery syntax

Link to the full query statement syntax: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax

query_statement:
    query_expr

query_expr:
    [ WITH with_query_name AS ( query_expr ) [, ...] ]
    select | ( query_expr )
    [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
    [ LIMIT count [ OFFSET skip_rows ] ]

select:
    SELECT [{ ALL | DISTINCT }]
        { [ expression. ]*
        | expression [ [ AS ] alias ] } [, ...]
    [ FROM from_item  [, ...] ]
    [ WHERE expression ]
    [ GROUP BY expression [, ...] ]
    [ HAVING expression ]

from_item: {
    table_name [ [ AS ] alias ]
    join |
    ( query_expr ) [ [ AS ] alias ] |
    with_query_name [ [ AS ] alias ]
}

join:
   from_item [ join_type ] JOIN from_item
   [ ON expression | USING ( join_column [, ...] ) ]

join_type:
   { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }

Where the expression is the following:

expression:
    literal_value |
    [ [ [ [ project_name . ] dataset_name . ] table_name . ] column_name ] |
    unary_operator expression |
    expression binary_operator expression |
    function_name ( [ expression [, ...] | * ] ) |
    ( expression [, ...] ) |
    CAST ( expression AS type_name ) |
    EXTRACT ( part FROM date_expression ) |
    expression IS [ NOT ] NULL | 
    expression IS [ NOT ] expression |
    expression [ NOT ] IN ( { expression | select } )

literal_value:
    numeric_literal |
    string_literal |
    NULL | TRUE | FALSE

unary_operator:
    NOT

binary_operator:
    AND | OR |
    < | <= | > | >= | = | <> | != 

Usage from source

To run the app

docker-compose up app

This will start Not-So-BigQuery in a container, and expose port 5443 on your host.

After this, you can configure your library to use http://localhost:5443/ as the API endpoint to reach BigQuery.

NodeJS

const bq = new BigQuery({ apiEndpoint: 'localhost:5443' });

Ruby

bigquery = Google::Cloud::Bigquery.new(endpoint: 'http://localhost:5443/')

To clear the storage

docker-compose rm -fs postgres && docker volume rm -f not-so-bigquery_db-data

Contributions

Contributions greatly appreciated.

not-so-bigquery's Issues

FORMAT_DATE

"w": The weekday (Sunday as the first day of the week) as a decimal number (1-7) instead of (0-6)

DATE_TRUNC

WEEK() is not supported

COUNTIF

There are issues when used with window specifications (see skipped test).

postgres Docker Compose image doesn't build

➜ DOCKER_BUILDKIT=0 docker-compose build postgres
Building postgres
Sending build context to Docker daemon  46.75MB
Step 1/3 : FROM clkao/postgres-plv8:10-2
10-2: Pulling from clkao/postgres-plv8
80369df48736: Already exists 
b18dd0a6efec: Already exists 
5c20c5b8227d: Already exists 
c5a7f905c8ec: Already exists 
5a3f55930dd8: Already exists 
ffc097878b09: Already exists 
3106d02490d4: Already exists 
88d1fc513b8f: Already exists 
f19250dffc5e: Pull complete 
756351b7a443: Pull complete 
0d24b08575ba: Pull complete 
31babd3be108: Pull complete 
16f2724dc303: Pull complete 
69802daaa561: Pull complete 
5153ff04d233: Pull complete 
Digest: sha256:954bb3db44088d7affd4e43557242455f44bc5bd8d111a55021d4e3fd3f20bb1
Status: Downloaded newer image for clkao/postgres-plv8:10-2
 ---> 0bc8adb515c5
Step 2/3 : COPY docker/postgres/disable_ipv6/etc/sysctl.conf /tmp/
COPY failed: file not found in build context or excluded by .dockerignore: stat docker/postgres/disable_ipv6/etc/sysctl.conf: file does not exist
ERROR: Service 'postgres' failed to build : Build failed

Is the custom Dockerfile.postgres really needed? It seems to just disable IPv6 - is that required?

DATE_DIFF

ISOWEEK, QUARTER, ISOWEEK and WEEK() is not supported