-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQL_Example.sql
43 lines (34 loc) · 1009 Bytes
/
SQL_Example.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
DELETE DATABASE spark;
DROP DATABASE spark;
CREATE DATABASE spark FROM DBC AS perm=1e9;
DATABASE spark;
GRANT ALL ON spark TO dbc;
CALL sysuif.replace_file('spark_script', 'spark_script.py', 'sz!/root/blog-master/spark/spark_script.py', 0);
SHOW FILE spark.spark_script;
SET SESSION SEARCHUIFDBPATH = spark;
drop table spark.query_ngrams;
-- Make sure there are rows in DBC.QryLogSQL
SELECT COUNT(*)
FROM DBC.QryLogSQL
;
CREATE TABLE spark.query_ngrams AS (
SELECT tab.QueryID
, CAST(tab.json_text AS JSON) AS ngrams
FROM SCRIPT (
ON (
SELECT TOP 100 QueryId
, SqlTextInfo
FROM DBC.QryLogSQL
) HASH BY QueryID
SCRIPT_COMMAND('python ./spark/spark_script.py')
RETURNS ('QueryID VARCHAR(30)', 'json_text CLOB')
) AS tab
) WITH DATA
PRIMARY INDEX (QueryID)
;
SELECT COUNT(ngrams)
FROM spark.query_ngrams
;
SELECT QueryID, ngrams (VARCHAR(2000))
FROM spark.query_ngrams
;