Skip to content
Mach DatabaseMach DatabaseMach Database

BigQuery dialect (GoogleSQL) through SQL tools

BigQuery’s client libraries and the bq tool talk to Google’s REST API, which MachWarehouse does not offer (owner decision pending, see docs/IDEAS.md). The dialect is supported: GoogleSQL queries run unchanged through MachWarehouse’s PostgreSQL wire and Arrow Flight SQL endpoints, so existing SQL files, dbt models written for BigQuery and BI dashboards keep their text.

The only change

Section titled “The only change”
# tool
 BigQuery console, bq query, client library
 psql, DBeaver, any PG or Flight SQL client
# connection
 Google project and credentials
 host=warehouse.internal port=5432 dbname=demo (or Flight SQL grpc+tls://warehouse.internal:32010)
# dialect
 GoogleSQL
 SET dialect = 'googlesql'; once per session, or options='-c dialect=googlesql' in the connection string
# queries
 queries.sql
 the same queries.sql, unchanged

Run with psql

Section titled “Run with psql”
Terminal window
psql "host=warehouse.internal port=5432 dbname=demo user=app options='-c dialect=googlesql' sslmode=verify-full" \
     -f ../dataset/googlesql.sql -f queries.sql

DBeaver

Section titled “DBeaver”

Create a PostgreSQL connection to warehouse.internal:5432, and in Driver properties set options to -c dialect=googlesql. The SQL editor then runs GoogleSQL text as is.

Source

Section titled “Source”

The program as it is kept and tested in the Mach Database Warehouse repository; highlighted lines read the connection setting.

queries.sql
-- GoogleSQL as written for BigQuery; runs unchanged on MachWarehouse with dialect = googlesql.


-- Revenue per country with a window share.
SELECT
  country,
  COUNT(*) AS events,
  SUM(revenue) AS revenue,
  ROUND(100 * SUM(revenue) / SUM(SUM(revenue)) OVER (), 1) AS revenue_share_pct
FROM demo.events
GROUP BY country
ORDER BY revenue DESC;


-- Arrays and UNNEST: devices seen per country.
SELECT country, ARRAY_AGG(DISTINCT device ORDER BY device) AS devices
FROM demo.events
GROUP BY country;


SELECT country, device
FROM (SELECT country, ARRAY_AGG(DISTINCT device) AS devices FROM demo.events GROUP BY country),
     UNNEST(devices) AS device
ORDER BY country, device;


-- STRUCT construction and QUALIFY: the best day per country.
SELECT country, STRUCT(event_date AS day, SUM(revenue) AS revenue) AS best_day
FROM demo.events
GROUP BY country, event_date
QUALIFY ROW_NUMBER() OVER (PARTITION BY country ORDER BY SUM(revenue) DESC) = 1;


-- Date functions and SAFE_CAST.
SELECT DATE_TRUNC(event_date, WEEK) AS week, SAFE_CAST(SUM(revenue) AS INT64) AS revenue
FROM demo.events
GROUP BY week
ORDER BY week;