Query Translation

Automatically translate popular SQL syntaxes into PostgreSQL when querying on bit.io

Suggest Edits

🚧

Experimental Feature

This feature may change in backward incompatible ways. The translator may not handle all source syntax. Translations may be invalid syntax. Translations may be valid syntax but semantically incorrect with unknown and unintended consequences. Semantics are not the same across SQL platforms even for identical syntax.

If you're more familiar with a SQL syntax other than PostgreSQL's or you just imported data from another database onto bit.io and have pre-existing queries in another syntax, you can try out bit.io's SQL syntax translator.

For example, if you just imported a SQLite database via our auto-importer that has the table foo, you can query that table using SQLite syntax:

#!translate:sqlite
select * from [foo]

To use the translator, start your query string with one of the following first lines (in order of recommendation):

  • #!translate:[source]
  • /*!translate:[source]*/
  • --!translate:[source] (do not use this with psql, it strips -- comments client-side)

where [source] is one of the following supported syntaxes:

  • bigquery
  • clickhouse
  • databricks
  • drill
  • duckdb
  • hive
  • mysql
  • oracle
  • presto
  • prql (include prql sql_dialect:postgres on the next line per PRQL docs)
  • redshift
  • snowflake
  • spark
  • sqlite
  • starrocks
  • tableau
  • trino
  • tsql

On every subsequent line, write SQL in that source's syntax. When bit.io receives the query, it will attempt to translate it into PostgreSQL. Note that the first line will be removed. Do not put any other commands on the first line.

Limitations

  • If query translation fails, the original query will still be submitted. Be careful! The #! directive should cause an error whereas others will not fail from the directive alone because the directives are comment characters. Be careful when using comment characters.

  • One source directive per query string. Subsequent directives in a single query are ignored.

  • Translation adds ~3-50ms latency, depending on query complexity and translator

  • Functions may not have the same semantics or exist in Postgres. Function names are not always translated.

 
Translation is done by https://github.com/tobymao/sqlglot and https://github.com/PRQL/prql. Use their documentation to understand the translator support and limits for each source syntax.

Updated 5 months ago