SnowFlake概念
Snowflake弹性数据仓库,简称Snowflake。Snowflake是一种多租户、事务性、安全、高度可扩展的弹性系统,具备完整的SQL支持和半结构化和schema-less数据模式支持。Snowflake在亚马逊云上提供现付即用的服务。用户只需将数据导入云上,就可以立即利用他们熟悉的工具和界面进行管理和查询。从2012年底,Snowflake开始计划实施,到2015年6月,Snowflake已经可以大体可用。现在,Snowflake被越来越多的大大小小的组织用于生产。这个系统每天运行几百万次查询在几PB的数据上。
SnowFlake基本操作
SNOWFLAKE SQL命令参考
Snowflake SQL命令(DDL、DML和查询语法)
DDL(数据定义语言)命令
基于帐户和基于会话的DDL
命令 | 注释 |
---|---|
ALTER ACCOUNT | 用于设置帐户级参数(仅限具有ACCOUNTADMIN角色的用户)。 |
ALTER SESSION | 用于设置当前会话中的参数。 |
SHOW FUNCTIONS | 显示系统定义的函数和用户定义的函数,包括外部函数。 |
SHOW PARAMETERS | 用于查看帐户或当前会话的参数设置。也可用于查看具有参数的对象的参数设置。 |
SHOW VARIABLES | 用于查看当前会话中的SQL变量 |
SET , UNSET | 用于在当前会话中设置和取消设置SQL变量。 |
USE { DATABASE,ROLE,SCHEMA,WAREHOUSE } | USE命令为当前会话设置数据库、角色、架构或仓库。 |
帐户对象DDL
命令 | 注释 |
---|---|
{ ALTER , CREATE ,DROP } CONNECTION | |
{ ALTER , CREATE ,DESC , DROP , UNDROP , USE } DATABASE | CREATE also supports cloning existing databases. |
{ ALTER ,CREATE } { API , NOTIFICATION , SECURITY , STORAGE } INTEGRATION | |
{ DESC , DROP } INTEGRATION | |
{ ALTER,CREATE , DESC, DROP } NETWORK POLICY | |
{ ALTER , CREATE, DESC , DROP } RESOURCE MONITOR | |
{ ALTER , CREATE , DESC , DROP , USE } ROLE | |
{ ALTER , CREATE , DESC , DROP } SESSION POLICY | |
{ ALTER ,CREATE, DESC , DROP } SHARE | |
{ ALTER ,CREATE ,DESC ,DROP } USER | |
{ ALTER ,CREATE ,DESC ,DROP , USE } WAREHOUSE | |
{ GRANT , REVOKE } PRIVILEGE | 特权并不是严格意义上的头等目标;但是,这些命令用于启用和管理Snowflake中所有一级对象的访问控制。 |
SHOW { DATABASES, INTEGRATIONS , NETWORK POLICIES, PRIVILEGES , RESOURCE MONITORS, ROLES, SESSION POLICIES , SHARES , USERS, WAREHOUSES } |
查询语法和运算符
Category | Constructs/Operators | Notes |
---|---|---|
Query Syntax | ||
[ WITH ] | CTE | |
SELECT [ DISTINCT ] | Required. | |
[ INTO ] | 用于将“雪花脚本”变量设置为结果列中的值。 | |
[ FROM ] | ||
[ AT / BEFORE ] | ||
[ CHANGES ] | ||
[ CONNECT BY ] | ||
[ JOIN ] | ||
[ MATCH_RECOGNIZE ] | ||
[ PIVOT | UNPIVOT ] | |
[ VALUES ] | ||
[ SAMPLE ] / [ TABLESAMPLE ] | ||
[ WHERE ] | ||
[ GROUP BY ] | ||
[ HAVING ] | ||
[ ORDER BY ] | ||
[ LIMIT ] | ||
Arithmetic Operators | ||
+ , - , * , \ , % | ||
Comparison Operators | ||
= , != , < > , > , >= , < , <= | ||
Logical/Boolean Operators | ||
AND , NOT , OR | ||
Set Operators | ||
INTERSECT [ ALL ] , MINUS [ ALL ] / EXCEPT , UNION [ ALL ] | ||
Subquery Operators | ||
ANY / ALL , [ NOT ] EXISTS , [ NOT ] IN | ||
Predicates | ||
BETWEEN , [ NOT ] EXISTS , [ NOT ] IN , LIKE |
TCL(事务控制语言)命令
命令 | 注释 |
---|---|
BEGIN , COMMIT , ROLLBACK | 用于当前会话中的多语句事务。 |
DESC TRANSACTIONS | |
SHOW { LOCKS , TRANSACTIONS } |
查询语法
Snowflake支持使用标准SELECT语句和以下基本语法进行查询:
[ WITH ... ]
SELECT
[ TOP <n> ]
...
[ INTO ... ]
[ FROM ...
[ AT | BEFORE ... ]
[ CHANGES ... ]
[ CONNECT BY ... ]
[ JOIN ... ]
[ LATERAL ... ]
[ MATCH_RECOGNIZE ... ]
[ PIVOT | UNPIVOT ... ]
[ VALUES ... ]
[ SAMPLE ... ] ]
[ WHERE ... ]
[ GROUP BY ...
[ HAVING ... ] ]
[ QUALIFY ... ]
[ ORDER BY ... ]
[ LIMIT ... ]
查询操作
Snowflake支持SQL:1999中定义的大多数标准运算符。
这些运算符包括算术运算符(如+和-)、集合运算符(如UNION)、子查询运算符(如ANY)等。