-
Notifications
You must be signed in to change notification settings - Fork 24
/
Copy pathwelcome.q
158 lines (114 loc) · 4.61 KB
/
welcome.q
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
\c 100 2000
\l aws.q
\l env.q
/ Source data directory
SOURCE_DATA_DIR:"hdb"
/ S3 bucket for external data and code
S3_DEST:"s3://",S3_BUCKET,"/data/",SOURCE_DATA_DIR,"/"
CODEBASE:"qcode"
CODE_PATH:"code/",CODEBASE,".zip"
CLUSTER_NODE_TYPE:"kx.s.xlarge"
NODE_COUNT:1
CACHE_SIZE:1200
/ Managed KX Database and Cluster names to create
DB_NAME:"welcomedb"
DELETE_CLUSTER:0b
DELETE_DATABASE:0b
create_delete:1b
now: .z.Z
$[create_delete=1b; [DB_NAME:"create_delete_db_",ssr[string(`date$now);".";""],"_",raze "0"^-2$string`hh`uu$now; DELETE_CLUSTER:1b; DELETE_DATABASE:1b ]; ];
CLUSTER_NAME:"cluster_",DB_NAME
.aws.set_prefs[`envId`userName`sessionName!(ENV_ID;KDB_USERNAME;"AWSCLI-Session") ];
/ FinSpace with Managed kdb Insights lifecycle example
/ -----------------------------------------------------------------
/ This q script mirrors the work performed in the welcome boto notebook
/ https://github.com/aws/amazon-finspace-examples/blob/main/ManagedkdbInsights/boto/welcome.ipynb
/ --
/ 1. Untar hdb.tar.gz for the hdb data
/ 2. Upload hdb to staging S3 bucket
/ 3. Create database
/ 4. Add HDB data to database
/ 5. Create a Cluster
/ 6. Get the connectionString
/ 7. Query Cluster
/ -----------------------------------------------------------------
/ 0. Check environment
/ -----------------------------------------------------------------
show .aws.get_kx_environment[ENV_ID]
/ 1. Untar hdb.tar.gz for the hdb data
/ -----------------------------------------------------------------
system ("tar -zxf hdb.tar.gz");
/show system("ls -la hdb")
/ 2. Upload hdb to staging S3 bucket
/ -----------------------------------------------------------------
system "aws s3 sync hdb ", S3_DEST;
/show system "aws s3 ls ", S3_DEST
/ 3. Create database
/ -----------------------------------------------------------------
.[.aws.create_kx_database;(DB_NAME;.aws.sdesc["demonstration database"]); {show "Database: ", DB_NAME, " already exists";}];
show "Created database: ", DB_NAME
show .aws.get_kx_database[DB_NAME];
/ 4. Add HDB data to database
/ -----------------------------------------------------------------
c_r:enlist `changeType`dbPath`s3Path!("PUT";"/";S3_DEST,())
res:.aws.create_kx_changeset[DB_NAME; c_r]
show "Adding Changeset: ", res`changesetId;
res:.aws.wait_for_status[.aws.get_kx_changeset;(DB_NAME; res`changesetId);("COMPLETED","FAILED");00:00:05;00:30:00]
show res
changesetId:res`changesetId
/ 5. Create a Cluster
/ -----------------------------------------------------------------
/ zip code
system "cd ", CODEBASE;
system "zip -r -X ../", CODEBASE, ".zip . -x '*.ipynb_checkpoints*'";
system "cd ..";
/ copy code to S3, show S3 contents
system "aws s3 cp ", CODEBASE, ".zip s3://", S3_BUCKET, "/code/", CODEBASE, ".zip";
/show system "aws s3 ls s3://", S3_BUCKET, "/code/";
CLUSTER_DESC: "Cluster from q API"
show "Creating Cluster: ", CLUSTER_NAME
res:.aws.create_kx_cluster[
CLUSTER_NAME;
"HDB";
CLUSTER_NODE_TYPE;
NODE_COUNT; "1.0";
AZ_MODE;
(
.aws.saz[AZ_ID];
.aws.svpc[VPC_ID;SECURITY_GROUP;SUBNET_ID;""];
.aws.sdbs[ .aws.db[DB_NAME;changesetId; .aws.cache["CACHE_1000";"/"]; ""] ];
.aws.scaches[.aws.ccache["CACHE_1000";1200]];
.aws.scode[S3_BUCKET;CODE_PATH;""];
.aws.sscript["init.q"];
.aws.scl[ ("s";"dbname")!("2";DB_NAME) ];
.aws.scdesc[CLUSTER_DESC]
)
]
show res
res:.aws.wait_for_status[.aws.get_kx_cluster;enlist CLUSTER_NAME;("RUNNING";"CREATE_FAILED");00:00:20;00:30:00]
show "Created Cluster: ", CLUSTER_NAME
show res
/ 6. Get the connectionString
/ -----------------------------------------------------------------
hdb:.aws.connect_to_cluster[CLUSTER_NAME;KDB_USERNAME]
/ 7. Query Cluster
/ -----------------------------------------------------------------
show "Querying cluster: ", CLUSTER_NAME
show hdb"tables[]"
show hdb"meta `example"
show hdb"select counts:count i, avg_num: avg number, avg_sq_num: avg sq number by date from example"
show hdb"count example"
/ clean up
/ -----------------------------------------------------------------
/ if deleting cluster ....
show "Deleting Cluster: ", CLUSTER_NAME
$[DELETE_CLUSTER=1b; [.aws.delete_kx_cluster[CLUSTER_NAME]] ]
.[.aws.wait_for_status; (.aws.get_kx_cluster;enlist CLUSTER_NAME;("DELETED";"DELETE_FAILED");00:00:20;01:00:00); {show "Cluster: ", CLUSTER_NAME, " does not exist, deleted";}];
show .aws.list_kx_clusters[ENV_ID];
/ if deleting database (and cluster was deleted) ...
show "Deleting Database: ", DB_NAME
$[DELETE_DATABASE=1b; [.aws.delete_kx_database[DB_NAME]] ]
show "Databases after delete"
show .aws.list_kx_databases[ENV_ID]
show "******** Done **********"
exit 0