go-zetasqlite

A database driver library that interprets ZetaSQL queries and runs them using SQLite3

Features

go-zetasqlite supports database/sql driver interface. So, you can use ZetaSQL queries just by importing github.com/goccy/go-zetasqlite. Also, go-zetasqlite uses SQLite3 as the database engine. Since we are using go-sqlite3, we can use the options ( like :memory: ) supported by go-sqlite3 ( see details ). ZetaSQL functionality is provided by go-zetasql

Installation

go get github.com/goccy/go-zetasqlite

NOTE

Since this library uses go-zetasql, the following environment variables must be enabled in order to build. See here for details.

CGO_ENABLED=1
CXX=clang++

Synopsis

You can pass ZetaSQL queries to Query/Exec function of database/sql package.

package main

import (
  "database/sql"
  "fmt"

  _ "github.com/goccy/go-zetasqlite"
)

func main() {
  db, err := sql.Open("zetasqlite", ":memory:")
  if err != nil {
    panic(err)
  }
  defer db.Close()

  rows, err := db.Query(`SELECT * FROM UNNEST([?, ?, ?])`, 1, 2, 3)
  if err != nil {
    panic(err)
  }
  var ids []int64
  for rows.Next() {
    var id int64
    if err := rows.Scan(&id); err != nil {
      panic(err)
    }
    ids = append(ids, id)
  }
  fmt.Println(ids) // [1 2 3]
}

Tools

ZetaSQLite CLI

You can execute ZetaSQL queries interactively by using the tools provided by cmd/zetasqlite-cli. See here for details

Status

A list of ZetaSQL ( Google Standard SQL ) specifications and features supported by go-zetasqlite.

Types

• INT64 ( INT, SMALLINT, INTEGER, BIGINT, TINYINT, BYTEINT )
• NUMERIC ( DECIMAL )
• BIGNUMERIC ( BIGDECIMAL )
• FLOAT64 ( FLOAT )
• BOOL ( BOOLEAN )
• STRING
• BYTES
• DATE
• TIME
• DATETIME
• TIMESTAMP
• INTERVAL
• ARRAY
• STRUCT
• JSON
• RECORD
• GEOGRAPHY

Expressions

Operators

• Field access operator
• Array subscript operator
• JSON subscript operator
• Unary operators ( +, -, ~ )
• Multiplication ( * )
• Division ( / )
• Concatenation operator ( || )
• Addition ( + )
• Subtraction ( - )
• Bitwise operators ( <<, >>, &, | )
• Comparison operators ( =, <, >, <=, >=, !=, <>)
• [NOT] LIKE
• [NOT] BETWEEN
• [NOT] IN
• IS [NOT] NULL
• IS [NOT] TRUE
• IS [NOT] FALSE
• NOT
• AND
• OR
• [NOT] EXISTS
• IS [NOT] DISTINCT FROM

Conditional Expressions

• CASE expr
• CASE
• COALESCE
• IFNULL
• NULLIF

Subqueries

• Expression subqueries
  • Scalar subqueries
  • ARRAY subqueries
  • IN subqueries
  • EXISTS subqueries
• Table subqueries
• Correlated subqueries
• Volatile subqueries

Query

• SELECT statement
  • SELECT *
  • SELECT expression
  • SELECT expression.*
  • SELECT * EXCEPT
  • SELECT * REPLACE
  • SELECT DISTINCT
  • SELECT ALL
  • SELECT AS STRUCT
  • SELECT AS VALUE
• FROM clause
• UNNEST operator
  • UNNEST and STRUCTs
  • Explicit and implicit UNNEST
  • UNNEST and NULLs
  • UNNEST and WITH OFFSET
• PIVOT operator
• UNPIVOT operator
• TABLESAMPLE operator
• JOIN operation
  • INNER JOIN
  • CROSS JOIN
  • Comma cross join (,)
  • FULL OUTER JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • ON clause
  • USING clause
  • ON and USING equivalency
  • Join operations in a sequence
  • Correlated join operation
• WHERE clause
• GROUP BY clause
• HAVING clause
  • Mandatory aggregation
• ORDER BY clause
• QUALIFY clause
• WINDOW clause
• Set operators
  • UNION
  • INTERSECT
  • EXCEPT
• LIMIT and OFFSET clauses
• WITH clause
  • RECURSIVE keyword
  • Non-recursive CTEs
  • Recursive CTEs
  • CTE rules and constraints
  • CTE visibility
• Using aliases
  • Explicit aliases
  • Implicit aliases
  • Alias visibility
  • Duplicate aliases
  • Ambiguous aliases
  • Range variables
• Value tables
  • Return query results as a value table
  • Create a table with a value table
  • Use a set operation on a value table
• Queries for wildcard table

Statements

DDL ( Data Definition Language )

• CREATE SCHEMA
• CREATE TABLE
• CREATE TABLE LIKE
• CREATE TABLE COPY
• CREATE SNAPSHOT TABLE
• CREATE TABLE CLONE
• CREATE VIEW
• CREATE MATERIALIZED VIEW
• CREATE EXTERNAL TABLE
• CREATE FUNCTION
• CREATE TABLE FUNCTION
• CREATE PROCEDURE
• CREATE ROW ACCESS POLICY
• CREATE CAPACITY
• CREATE RESERVATION
• CREATE ASSIGNMENT
• CREATE SEARCH INDEX
• ALTER SCHEMA SET DEFAULT COLLATE
• ALTER SCHEMA SET OPTIONS
• ALTER TABLE SET OPTIONS
• ALTER TABLE ADD COLUMN
• ALTER TABLE RENAME TO
• ALTER TABLE RENAME COLUMN
• ALTER TABLE DROP COLUMN
• ALTER TABLE SET DEFAULT COLLATE
• ALTER COLUMN SET OPTIONS
• ALTER COLUMN DROP NOT NULL
• ALTER COLUMN SET DATA TYPE
• ALTER COLUMN SET DEFAULT
• ALTER COLUMN DROP DEFAULT
• ALTER VIEW SET OPTIONS
• ALTER MATERIALIZED VIEW SET OPTIONS
• ALTER ORGANIZATION SET OPTIONS
• ALTER PROJECT SET OPTIONS
• ALTER BI_CAPACITY SET OPTIONS
• DROP SCHEMA
• DROP TABLE
• DROP SNAPSHOT TABLE
• DROP EXTERNAL TABLE
• DROP VIEW
• DROP MATERIALIZED VIEW
• DROP FUNCTION
• DROP TABLE FUNCTION
• DROP PROCEDURE
• DROP ROW ACCESS POLICY
• DROP CAPACITY
• DROP RESERVATION
• DROP ASSIGNMENT
• DROP SEARCH INDEX

DML ( Data Manipulation Language )

• INSERT
• DELETE
• TRUNCATE TABLE
• UPDATE
• MERGE

DCL ( Data Control Language )

• GRANT
• REVOKE

Procedural Language

• DECLARE
• SET
• EXECUTE IMMEDIATE
• BEGIN...END
• BEGIN...EXCEPTION...END
• CASE
• CASE search_expression
• IF
• Labels
• Loops
  • LOOP
  • REPEATE
  • WHILE
  • BREAK
  • LEAVE
  • CONTINUE
  • ITERATE
  • FOR...IN
• Transactions
  • BEGIN TRANSACTION
  • COMMIT TRANSACTION
  • ROLLBACK TRANSACTION
• RAISE
• RETURN
• CALL

Debugging Statements

• ASSERT

Other Statements

• EXPORT DATA
• LOAD DATA

User Defined Functions

• User Defined Function

• Templated Argument Function

  • If the return type is not specified, templated argument function supports only some types of patterns.
    • ANY -> ANY
    • ARRAY<ANY> -> ANY
    • ANY -> ARRAY<ANY>
    • If the return type is always fixed, only some types are supported, such as INT64 / DOUBLE

• JavaScript UDF

Functions

Aggregate functions

• ANY_VALUE
• ARRAY_AGG
• ARRAY_CONCAT_AGG
• AVG
• BIT_AND
• BIT_OR
• BIT_XOR
• COUNT
• COUNTIF
• LOGICAL_AND
• LOGICAL_OR
• MAX
• MIN
• STRING_AGG
• SUM

Statistical aggregate functions

• CORR
• COVAR_POP
• COVAR_SAMP
• STDDEV_POP
• STDDEV_SAMP
• STDDEV
• VAR_POP
• VAR_SAMP
• VARIANCE

Approximate aggregate functions

• APPROX_COUNT_DISTINCT
• APPROX_QUANTILES
• APPROX_TOP_COUNT
• APPROX_TOP_SUM

HyperLogLog++ functions

• HLL_COUNT.INIT
• HLL_COUNT.MERGE
• HLL_COUNT.MERGE_PARTIAL
• HLL_COUNT.EXTRACT

Numbering functions

• RANK
• DENSE_RANK
• PERCENT_RANK
• CUME_DIST
• NTILE
• ROW_NUMBER

Bit functions

• BIT_COUNT

Conversion functions

• CAST AS ARRAY
• CAST AS BIGNUMERIC
• CAST AS BOOL
• CAST AS BYTES
• CAST AS DATE
• CAST AS DATETIME
• CAST AS FLOAT64
• CAST AS INT64
• CAST AS INTERVAL
• CAST AS NUMERIC
• CAST AS STRING
• CAST AS STRUCT
• CAST AS TIME
• CAST AS TIMESTAMP
• PARSE_BIGNUMERIC
• PARSE_NUMERIC
• SAFE_CAST
• Format clause for CAST

Mathematical functions

• ABS
• SIGN
• IS_INF
• IS_NAN
• IEEE_DIVIDE
• RAND
• SQRT
• POW
• POWER
• EXP
• LN
• LOG
• LOG10
• GREATEST
• LEAST
• DIV
• SAFE_DIVIDE
• SAFE_MULTIPLY
• SAFE_NEGATE
• SAFE_ADD
• SAFE_SUBTRACT
• MOD
• ROUND
• TRUNC
• CEIL
• CEILING
• FLOOR
• COS
• COSH
• ACOS
• ACOSH
• SIN
• SINH
• ASIN
• ASINH
• TAN
• TANH
• ATAN
• ATANH
• ATAN2
• RANGE_BUCKET

Navigation functions

• FIRST_VALUE
• LAST_VALUE
• NTH_VALUE
• LEAD
• LAG
• PERCENTILE_CONT
• PERCENTILE_DISC

Hash functions

• FARM_FINGERPRINT
• MD5
• SHA1
• SHA256
• SHA512

String functions

• ASCII
• BYTE_LENGTH
• CHAR_LENGTH
• CHARACTER_LENGTH
• CHR
• CODE_POINTS_TO_BYTES
• CODE_POINTS_TO_STRING
• COLLATE
• CONCAT
• CONTAINS_SUBSTR
• ENDS_WITH
• FORMAT
• FROM_BASE32
• FROM_BASE64
• FROM_HEX
• INITCAP
• INSTR
• LEFT
• LENGTH
• LPAD
• LOWER
• LTRIM
• NORMALIZE
• NORMALIZE_AND_CASEFOLD
• OCTET_LENGTH
• REGEXP_CONTAINS
• REGEXP_EXTRACT
• REGEXP_EXTRACT_ALL
• REGEXP_INSTR
• REGEXP_REPLACE
• REGEXP_SUBSTR
• REPLACE
• REPEAT
• REVERSE
• RIGHT
• RPAD
• RTRIM
• SAFE_CONVERT_BYTES_TO_STRING
• SOUNDEX
• SPLIT
• STARTS_WITH
• STRPOS
• SUBSTR
• SUBSTRING
• TO_BASE32
• TO_BASE64
• TO_CODE_POINTS
• TO_HEX
• TRANSALTE
• TRIM
• UNICODE
• UPPER

JSON functions

• JSON_EXTRACT
• JSON_QUERY
• JSON_EXTRACT_SCALAR
• JSON_VALUE
• JSON_EXTRACT_ARRAY
• JSON_QUERY_ARRAY
• JSON_EXTRACT_STRING_ARRAY
• JSON_VALUE_ARRAY
• PARSE_JSON
• TO_JSON
• TO_JSON_STRING
• STRING
• BOOL
• INT64
• FLOAT64
• JSON_TYPE

Array functions

• ARRAY
• ARRAY_CONCAT
• ARRAY_LENGTH
• ARRAY_TO_STRING
• GENERATE_ARRAY
• GENERATE_DATE_ARRAY
• GENERATE_TIMESTAMP_ARRAY
• ARRAY_REVERSE

Date functions

• CURRENT_DATE
• EXTRACT
• DATE
• DATE_ADD
• DATE_SUB
• DATE_DIFF
• DATE_TRUNC
• DATE_FROM_UNIX_DATE
• FORMAT_DATE
• LAST_DAY
• PARSE_DATE
• UNIX_DATE

Datetime functions

• CURRENT_DATETIME
• DATETIME
• EXTRACT
• DATETIME_ADD
• DATETIME_SUB
• DATETIME_DIFF
• DATETIME_TRUNC
• FORMAT_DATETIME
• LAST_DAY
• PARSE_DATETIME

Time functions

• CURRENT_TIME
• TIME
• EXTRACT
• TIME_ADD
• TIME_SUB
• TIME_DIFF
• TIME_TRUNC
• FORMAT_TIME
• PARSE_TIME

Timestamp functions

• CURRENT_TIMESTAMP
• EXTRACT
• STRING
• TIMESTAMP
• TIMESTAMP_ADD
• TIMESTAMP_SUB
• TIMESTAMP_DIFF
• TIMESTAMP_TRUNC
• FORMAT_TIMESTAMP
• PARSE_TIMESTAMP
• TIMESTAMP_SECONDS
• TIMESTAMP_MILLIS
• TIMEATAMP_MICROS
• UNIX_SECONDS
• UNIX_MILLIS
• UNIX_MICROS

Interval functions

• MAKE_INTERVAL
• EXTRACT
• JUSTIFY_DAYS
• JUSTIFY_HOURS
• JUSTIFY_INTERVAL

Geography functions

• S2_CELLIDFROMPOINT
• S2_COVERINGCELLIDS
• ST_ANGLE
• ST_AREA
• ST_ASBINARY
• ST_ASGEOJSON
• ST_ASTEXT
• ST_AZIMUTH
• ST_BOUNDARY
• ST_BOUNDINGBOX
• ST_BUFFER
• ST_BUFFERWITHTOLERANCE
• ST_CENTROID
• ST_CENTROID_AGG
• ST_CLOSESTPOINT
• ST_CLUSTERDBSCAN
• ST_CONTAINS
• ST_CONVEXHULL
• ST_COVEREDBY
• ST_COVERS
• ST_DIFFERENCE
• ST_DIMENSION
• ST_DISJOINT
• ST_DISTANCE
• ST_DUMP
• ST_DWITHIN
• ST_ENDPOINT
• ST_EQUALS
• ST_EXTENT
• ST_EXTERIORRING
• ST_GEOGFROM
• ST_GEOGFROMGEOJSON
• ST_GEOGFROMTEXT
• ST_GEOGFROMWKB
• ST_GEOGPOINT
• ST_GEOGPOINTFROMGEOHASH
• ST_GEOHASH
• ST_GEOMETRYTYPE
• ST_INTERIORRINGS
• ST_INTERSECTION
• ST_INTERSECTS
• ST_INTERSECTSBOX
• ST_ISCLOSED
• ST_ISCOLLECTION
• ST_ISEMPTY
• ST_ISRING
• ST_LENGTH
• ST_MAKELINE
• ST_MAKEPOLYGON
• ST_MAKEPOLYGONORIENTED
• ST_MAXDISTANCE
• ST_NPOINTS
• ST_NUMGEOMETRIES
• ST_NUMPOINTS
• ST_PERIMETER
• ST_POINTN
• ST_SIMPLIFY
• ST_SNAPTOGRID
• ST_STARTPOINT
• ST_TOUCHES
• ST_UNION
• ST_UNION_AGG
• ST_WITHIN
• ST_X
• ST_Y

Security functions

• SESSION_USER

UUID functions

• GENERATE_UUID

Net functions

• NET.IP_FROM_STRING
• NET.SAFE_IP_FROM_STRING
• NET.IP_TO_STRING
• NET.IP_NET_MASK
• NET.IP_TRUNC
• NET.IPV4_FROM_INT64
• NET.IPV4_TO_INT64
• NET.HOST
• NET.PUBLIC_SUFFIX
• NET.REG_DOMAIN

Debugging functions

• ERROR

AEAD encryption functions

• KEYS.NEW_KEYSET
• KEYS.ADD_KEY_FROM_RAW_BYTES
• AEAD.DECRYPT_BYTES
• AEAD.DECRYPT_STRING
• AEAD.ENCRYPT
• DETERMINISTIC_DECRYPT_BYTES
• DETERMINISTIC_DECRYPT_STRING
• DETERMINISTIC_ENCRYPT
• KEYS.KEYSET_CHAIN
• KEYS.KEYSET_FROM_JSON
• KEYS.KEYSET_TO_JSON
• KEYS.ROTATE_KEYSET
• KEYS.KEYSET_LENGTH

License

MIT