Skip to content

Latest commit

 

History

History
730 lines (630 loc) · 13.6 KB

README.md

File metadata and controls

730 lines (630 loc) · 13.6 KB

go-zetasqlite

Go GoDoc

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