A database driver library that interprets ZetaSQL queries and runs them using SQLite3
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
go get github.com/goccy/go-zetasqlite
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++
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]
}
You can execute ZetaSQL queries interactively by using the tools provided by cmd/zetasqlite-cli
. See here for details
A list of ZetaSQL ( Google Standard SQL ) specifications and features supported by go-zetasqlite.
- 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
- 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
- CASE expr
- CASE
- COALESCE
- IFNULL
- NULLIF
- Expression subqueries
- Scalar subqueries
- ARRAY subqueries
- IN subqueries
- EXISTS subqueries
- Table subqueries
- Correlated subqueries
- Volatile subqueries
- 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
- 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
- INSERT
- DELETE
- TRUNCATE TABLE
- UPDATE
- MERGE
- GRANT
- REVOKE
- 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
- ASSERT
- EXPORT DATA
- LOAD DATA
-
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
- If the return type is not specified, templated argument function supports only some types of patterns.
-
JavaScript UDF
- 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
- CORR
- COVAR_POP
- COVAR_SAMP
- STDDEV_POP
- STDDEV_SAMP
- STDDEV
- VAR_POP
- VAR_SAMP
- VARIANCE
- APPROX_COUNT_DISTINCT
- APPROX_QUANTILES
- APPROX_TOP_COUNT
- APPROX_TOP_SUM
- HLL_COUNT.INIT
- HLL_COUNT.MERGE
- HLL_COUNT.MERGE_PARTIAL
- HLL_COUNT.EXTRACT
- RANK
- DENSE_RANK
- PERCENT_RANK
- CUME_DIST
- NTILE
- ROW_NUMBER
- BIT_COUNT
- 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
- 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
- FIRST_VALUE
- LAST_VALUE
- NTH_VALUE
- LEAD
- LAG
- PERCENTILE_CONT
- PERCENTILE_DISC
- FARM_FINGERPRINT
- MD5
- SHA1
- SHA256
- SHA512
- 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_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
- ARRAY_CONCAT
- ARRAY_LENGTH
- ARRAY_TO_STRING
- GENERATE_ARRAY
- GENERATE_DATE_ARRAY
- GENERATE_TIMESTAMP_ARRAY
- ARRAY_REVERSE
- CURRENT_DATE
- EXTRACT
- DATE
- DATE_ADD
- DATE_SUB
- DATE_DIFF
- DATE_TRUNC
- DATE_FROM_UNIX_DATE
- FORMAT_DATE
- LAST_DAY
- PARSE_DATE
- UNIX_DATE
- CURRENT_DATETIME
- DATETIME
- EXTRACT
- DATETIME_ADD
- DATETIME_SUB
- DATETIME_DIFF
- DATETIME_TRUNC
- FORMAT_DATETIME
- LAST_DAY
- PARSE_DATETIME
- CURRENT_TIME
- TIME
- EXTRACT
- TIME_ADD
- TIME_SUB
- TIME_DIFF
- TIME_TRUNC
- FORMAT_TIME
- PARSE_TIME
- 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
- MAKE_INTERVAL
- EXTRACT
- JUSTIFY_DAYS
- JUSTIFY_HOURS
- JUSTIFY_INTERVAL
- 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
- SESSION_USER
- GENERATE_UUID
- 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
- ERROR
- 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
MIT