github.com/yokomotod/go-zetasqlite

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]
}

Status

A list of ZetaSQL 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

Statements

• SELECT
• UPDATE
• INSERT
• DELETE
• DROP
• TRUNCATE
• MERGE
• BEGIN-END
• BEGIN TRANSACTION
• COMMIT TRANSACTION
• CREATE TABLE
• CREATE FUNCTION
• CREATE TEMPORARY TABLE
• CREATE TEMPORARY FUNCTION

Standard SQL Features

Operator precedence

• 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-WHEN
• COALESCE
• IF
• IFNULL
• NULLIF

Other clauses

• OVER
• WINDOW
• WITH
• UNION
• HAVING
• ORDER BY
• GROUP BY - ROLLUP
• INNER/LEFT/RIGHT/FULL/CROSS JOIN
• QUALIFY
• EXCEPT
• REPLACE

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

Not suported yet

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