持续更新中……

SQL：

--  Multi-row insert

INSERT INTO students VALUES

    ('Bob Brown', '456 Taylor St, Cupertino', 444444),

    ('Cathy Johnson', '789 Race Ave, Palo Alto', 555555);

--  恢复表数据到某一版本

RESTORE TABLE employee TO VERSION AS OF 100;

--  仅查询某一版本

SELECT * FROM employee 

TIMESTAMP AS OF '2024-09-06'

--  直接查询csv文件

SELECT * FROM

csv.`/mnt/daily/employee.csv`;

SELECT * FROM

  read_files(

    '/mnt/daily/employee.csv',

    header => "True",

    sep => ","

  ) 

-- 函数

SELECT contains('SparkSQL', 'SQL'), 

       contains('SparkSQL', 'Spork');

SELECT startswith('SparkSQL', 'Spark'),

         endswith('SparkSQL', 'dataframes');

SELECT array_agg(col) FROM VALUES (1), (2), (NULL), (1) AS tab(col);

-- 2022-03-31 minus 1 month yields 2022-02-31.

SELECT dateadd(MONTH, -1, TIMESTAMP'2022-03-31 00:00:00');

-- One month has passed even though it's not the end of the month yet because

-- the day and time line up.

SELECT datediff(MONTH, TIMESTAMP'2021-02-28 12:00:00', TIMESTAMP'2021-03-28 12:00:00');

SELECT array_agg(col) FROM VALUES (1), (2), (NULL), (1) AS tab(col);

-- 建表（deep clone）

create or replace table employee

deep clone delta.`abfss://test01.dfs.core.chinacloudapi.cn/dwd/employee`

location 'dbfs:/mnt/dwd/employee'

-- 表增加多个字段：

ALTER TABLE table_test

 ADD COLUMNS (LastModifiedBy string COMMENT "Last changed by", LastModifiedDate timestamp COMMENT "Last changed time");.

-- 调用notebook

dbutils.notebook.run("/Workspace/masterdata/employee", 300)

Python：

-- 删除表（External）

dbutils.fs.rm("dbfs:/mnt/ods/ods_employee",True);

[配合SQL使用：drop table if exists ods_employee;]

--生成全字段SELECT语句

def generate_select(schema_name, table_name):

    df = spark.sql(f'show columns in {schema_name}.{table_name}').collect()

    print('SELECT')

    print('  ' + ',\n  '.join([row[0] for row in df]))

    print(f'FROM {schema_name}.{table_name}')

generate_select('tpcds001tb', 'customer')