-
Notifications
You must be signed in to change notification settings - Fork 77
Expand file tree
/
Copy pathtestDatabricks.java
More file actions
299 lines (287 loc) · 19.8 KB
/
Copy pathtestDatabricks.java
File metadata and controls
299 lines (287 loc) · 19.8 KB
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
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
package scriptWriter;
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TGSqlParser;
import junit.framework.TestCase;
public class testDatabricks extends TestCase
{
public void test1( )
{
TGSqlParser sqlparser = new TGSqlParser( EDbVendor.dbvdatabricks);
sqlparser.sqltext = "select datepart('days',interval 5 days 3 hours 7 minutes);";
sqlparser.parse( );
assertTrue(testScriptGenerator.verifyScript(EDbVendor.dbvdatabricks, sqlparser.sqlstatements.get(0).toString(), sqlparser.sqlstatements.get(0).toScript()));
}
public void test2( )
{
TGSqlParser sqlparser = new TGSqlParser( EDbVendor.dbvdatabricks);
sqlparser.sqltext = "select datepart('seconds',interval 5 hours 30 seconds 1 milliseconds 1 microseconds);\n" +
"select datepart('month',interval '2021-11' year to month);\n" +
"select datepart('minute',interval '123 23:55:59.002001' day to second);\n" +
"select interval '1-1' year to month div interval '-1' month;\n" +
"select extract(days from interval 5 days 3 hours 7 minutes);\n" +
"select extract(seconds from interval 5 hours 30 seconds 1 milliseconds 1 microseconds);\n" +
"select extract(month from interval '2021-11' year to month);\n" +
"select extract(minute from interval '123 23:55:59.002001' day to second);\n" +
"select sequence(to_date('2018-01-01'),to_date('2018-03-01'),interval 1 month);\n" +
"select sequence(to_date('2018-01-01'),to_date('2018-03-01'),interval '0-1' year to month);\n" +
"select sign(interval -'100' year);\n" +
"select signum(interval -'100' year);\n" +
"select try_add(date'2021-01-01',interval 1 year);\n" +
"select try_add(timestamp'2021-01-01 00:00:00',interval 1 day);\n" +
"select try_add(interval 1 year,interval 2 year);\n" +
"select try_divide(interval 2 month,2);\n" +
"select try_divide(interval 2 month,0);\n" +
"select try_multiply(interval 2 year,3);\n" +
"select try_subtract(date'2021-01-01',interval 1 year);\n" +
"select try_subtract(timestamp'2021-01-02 00:00:00',interval 1 day);\n" +
"select try_subtract(interval 2 year,interval 1 year);\n" +
"select width_bucket(interval '0' year,interval '0' year,interval '10' year,10);\n" +
"select width_bucket(interval '1' year,interval '0' year,interval '10' year,10);\n" +
"select width_bucket(interval '0' day,interval '0' day,interval '10' day,10);\n" +
"select width_bucket(interval '1' day,interval '0' day,interval '10' day,10);\n";
sqlparser.parse( );
for(int i=0;i<sqlparser.sqlstatements.size();i++){
assertTrue(testScriptGenerator.verifyScript(EDbVendor.dbvdatabricks,
sqlparser.sqlstatements.get(i).toString(), sqlparser.sqlstatements.get(i).toScript()));
}
}
public void test3( )
{
TGSqlParser sqlparser = new TGSqlParser( EDbVendor.dbvdatabricks);
sqlparser.sqltext = "select contains('spark sql','spark')";
sqlparser.parse( );
assertTrue(testScriptGenerator.verifyScript(EDbVendor.dbvdatabricks, sqlparser.sqlstatements.get(0).toString(), sqlparser.sqlstatements.get(0).toScript()));
}
public void test4( )
{
TGSqlParser sqlparser = new TGSqlParser( EDbVendor.dbvdatabricks);
sqlparser.sqltext = "select exists(array(1,2,3),x -> x % 2 == 0)";
sqlparser.parse( );
assertTrue(testScriptGenerator.verifyScript(EDbVendor.dbvdatabricks, sqlparser.sqlstatements.get(0).toString(), sqlparser.sqlstatements.get(0).toScript()));
}
public void test5( )
{
TGSqlParser sqlparser = new TGSqlParser( EDbVendor.dbvdatabricks);
sqlparser.sqltext = "select date_part('year',timestamp '2019-08-12 01:00:00.123456')";
sqlparser.parse( );
assertTrue(testScriptGenerator.verifyScript(EDbVendor.dbvdatabricks, sqlparser.sqlstatements.get(0).toString(), sqlparser.sqlstatements.get(0).toScript()));
}
public void test6( )
{
TGSqlParser sqlparser = new TGSqlParser( EDbVendor.dbvdatabricks);
sqlparser.sqltext = "SELECT *\n" +
"FROM table1 t6\n" +
" JOIN table2 t7 ON ( (t6.ssn = t7.ssn))\n" +
" LEFT OUTER JOIN table3 t8\n" +
" ON ( (t6.ssn = t8.ssn\n" +
" AND t6.scc = t8.scc\n" +
" AND t6.sfy = t8.sfy\n" +
" AND t6.sli = t8.sli\n" +
" AND t6.sdn = t8.sdn)) LIMIT 10000";
sqlparser.parse( );
assertTrue(testScriptGenerator.verifyScript(EDbVendor.dbvdatabricks, sqlparser.sqlstatements.get(0).toString(), sqlparser.sqlstatements.get(0).toScript()));
}
public void test7( )
{
TGSqlParser sqlparser = new TGSqlParser( EDbVendor.dbvdatabricks);
sqlparser.sqltext = "SELECT *\n" +
"FROM ((((((((table0 t0\n" +
" JOIN table1 t1 ON ( (t0.ssn = t1.ssn)))\n" +
" JOIN table2 t2 ON ( (CAST (t0.cd AS DATE) = t2.cd)))\n" +
" JOIN table3 t3\n" +
" ON ( (t0.phi = t3.phi\n" +
" AND t0.ssn = t3.ssn)))\n" +
" JOIN table4 t4\n" +
" ON ( (t3.phi = t4.phi\n" +
" AND t3.pli = t4.pli\n" +
" AND t3.ssn = t4.ssn)))\n" +
" JOIN table5 t5\n" +
" ON ( (t0.oi = t5.oi\n" +
" AND t0.ssn = t5.ssn)))\n" +
" JOIN table6 t6\n" +
" ON ( (t5.li = t6.li\n" +
" AND t5.ssn = t6.ssn)))\n" +
" LEFT JOIN table7 t7\n" +
" ON ( (t0.vsi = t7.vsi\n" +
" AND t0.ssn = t7.ssn)))\n" +
" JOIN table8 xgcc\n" +
" ON ( (t4.cci = xgcc.cci\n" +
" AND t4.ssn = xgcc.ssn)))\n" +
" LEFT JOIN table9 t9\n" +
" ON ( (t0.terms_id = t9.term_id\n" +
" AND t0.ssn = t9.ssn))\n" +
" LEFT JOIN table10 t10\n" +
" ON t0.ssn = t10.ssn\n" +
" AND t0.vsi = t10.vsi";
sqlparser.parse( );
assertTrue(testScriptGenerator.verifyScript(EDbVendor.dbvdatabricks, sqlparser.sqlstatements.get(0).toString(), sqlparser.sqlstatements.get(0).toScript()));
}
public void test8( )
{
TGSqlParser sqlparser = new TGSqlParser( EDbVendor.dbvdatabricks);
sqlparser.sqltext = "WITH base AS\n" +
"(\n" +
" SELECT ccl1,\n" +
" 101 AS prev_year_value,\n" +
" 102 AS prev_qtr_value,\n" +
" 'Quarterly' AS period,\n" +
" 103 AS curr_value\n" +
" FROM table0 hb\n" +
" WHERE 1 = 1\n" +
")\n" +
"SELECT ccl1,\n" +
" base.curr_value AS curr_value,\n" +
" IF (period LIKE 'Quarterly',COALESCE((base.curr_value - base.prev_qtr_value),0) /COALESCE(ABS(base.prev_qtr_value),1)*100,'-') AS VPQ,\n" +
" IF (period IN ('Quarterly','YTD'),COALESCE((base.curr_value - base.prev_year_value),0) /COALESCE(ABS(base.prev_year_value),1)*100,'-') AS VPY\n" +
"FROM base\n" +
"WHERE 1 = 1";
sqlparser.parse( );
assertTrue(testScriptGenerator.verifyScript(EDbVendor.dbvdatabricks, sqlparser.sqlstatements.get(0).toString(), sqlparser.sqlstatements.get(0).toScript()));
}
public void test9( )
{
TGSqlParser sqlparser = new TGSqlParser( EDbVendor.dbvdatabricks);
sqlparser.sqltext = "select 3 ^ 5";
sqlparser.parse( );
assertTrue(testScriptGenerator.verifyScript(EDbVendor.dbvdatabricks, sqlparser.sqlstatements.get(0).toString(), sqlparser.sqlstatements.get(0).toScript()));
}
public void test10( )
{
TGSqlParser sqlparser = new TGSqlParser( EDbVendor.dbvdatabricks);
sqlparser.sqltext = "WITH base AS\n" +
"(\n" +
" SELECT cost_category_level1,\n" +
" SUM(CASE WHEN (to_date (CONCAT (hb.period_year,'-',hb.period_num,'-01'),'yyyy-MM-dd') BETWEEN to_date ('2023-04-01','yyyy-MM-dd') -INTERVAL 1 YEAR AND to_date ('2023-06-30','yyyy-MM-dd') -INTERVAL 1 YEAR) THEN hb.period_balance_amount ELSE 0 END) AS prev_year_value,\n" +
" SUM(CASE WHEN (hb.period_year = YEAR(to_date ('2023-06-30','yyyy-MM-dd')) -(FLOOR((12 -MONTH(to_date ('2023-06-30','yyyy-MM-dd'))) / 9)) AND hb.period_num = (MONTH(to_date ('2023-06-30','yyyy-MM-dd')) + 8) % 12 + 1) THEN hb.qtd_balance_amount ELSE 0 END) AS prev_qtr_value,\n" +
" CASE\n" +
" WHEN DAY(to_date ('2023-04-01','yyyy-MM-dd')) IN ('01','1') AND MONTH(to_date ('2023-04-01','yyyy-MM-dd')) IN ('01','1') AND DAY(to_date ('2023-06-30','yyyy-MM-dd')) IN ('31') AND MONTH(to_date ('2023-06-30','yyyy-MM-dd')) IN ('03','3') AND YEAR(to_date ('2023-04-01','yyyy-MM-dd')) = YEAR(to_date ('2023-06-30','yyyy-MM-dd')) OR DAY(to_date ('2023-04-01','yyyy-MM-dd')) IN ('01','1') AND MONTH(to_date ('2023-04-01','yyyy-MM-dd')) IN ('04','4') AND DAY(to_date ('2023-06-30','yyyy-MM-dd')) IN ('30') AND MONTH(to_date ('2023-06-30','yyyy-MM-dd')) IN ('06','6') AND YEAR(to_date ('2023-04-01','yyyy-MM-dd')) = YEAR(to_date ('2023-06-30','yyyy-MM-dd')) OR DAY(to_date ('2023-04-01','yyyy-MM-dd')) IN ('01','1') AND MONTH(to_date ('2023-04-01','yyyy-MM-dd')) IN ('07','7') AND DAY(to_date ('2023-06-30','yyyy-MM-dd')) IN ('30') AND MONTH(to_date ('2023-06-30','yyyy-MM-dd')) IN ('09','9') AND YEAR(to_date ('2023-04-01','yyyy-MM-dd')) = YEAR(to_date ('2023-06-30','yyyy-MM-dd')) OR DAY(to_date ('2023-04-01','yyyy-MM-dd')) IN ('01','1') AND MONTH(to_date ('2023-04-01','yyyy-MM-dd')) IN ('10') AND DAY(to_date ('2023-06-30','yyyy-MM-dd')) IN ('31') AND MONTH(to_date ('2023-06-30','yyyy-MM-dd')) IN ('12') AND YEAR(to_date ('2023-04-01','yyyy-MM-dd')) = YEAR(to_date ('2023-06-30','yyyy-MM-dd')) THEN 'Quarterly'\n" +
" WHEN DAY(to_date ('2023-04-01','yyyy-MM-dd')) IN ('01','1') AND MONTH(to_date ('2023-04-01','yyyy-MM-dd')) IN ('01','1') AND to_date ('2023-06-30','yyyy-MM-dd') = CURRENT_DATE() THEN 'YTD'\n" +
" ELSE 'Custom'\n" +
" END AS period,\n" +
" SUM(CASE WHEN (to_date (CONCAT (hb.period_year,'-',hb.period_num,'-01'),'yyyy-MM-dd') BETWEEN to_date ('2023-04-01','yyyy-MM-dd') AND to_date ('2023-06-30','yyyy-MM-dd')) THEN hb.period_balance_amount ELSE 0 END) AS curr_value\n" +
" FROM fdl_db_qa_dbr_consumption_inc.xx_hfm_balances hb\n" +
" JOIN fdl_db_qa_dbr_consumption_inc.xx_drm_hier_account dha\n" +
" ON hb.account = dha.name\n" +
" AND dha.drm_type = 'ABC_D'\n" +
" AND dha.drm_org = 'AB'\n" +
" JOIN fdl_db_qa_dbr_consumption_inc.xx_drm_hier_supplementaldata dhs\n" +
" ON dhs.name = hb.supplemental_dim\n" +
" AND dhs.drm_type = 'ABCD'\n" +
" AND dhs.drm_org = 'AB'\n" +
" JOIN fdl_db_qa_dbr_consumption_inc.xx_drm_hier_reporting dhr\n" +
" ON dhr.name = hb.reporting_dim\n" +
" AND dhr.drm_type = 'ABCD'\n" +
" AND dhr.drm_org = 'AB'\n" +
" JOIN fdl_db_qa_dbr_consumption_inc.xx_gl_code_combinations xgcc\n" +
" ON hb.source_system_name = xgcc.source_system_name\n" +
" AND hb.code_combination_id = xgcc.CODE_COMBINATION_ID\n" +
" WHERE hb.currency = 'USD'\n" +
" AND hb.scenario = 'Actual'\n" +
" AND hb.data_view = 'YTD'\n" +
" AND dhr.level03_parent_node = 'QQ_TorCrAsRettttpo'\n" +
" AND dhs.level03_parent_node = 'AB_RPEE'\n" +
" AND (to_date(CONCAT(hb.period_year,'-',hb.period_num,'-01'),'yyyy-MM-dd') BETWEEN to_date('2023-04-01','yyyy-MM-dd') -INTERVAL 1 YEAR AND to_date('2023-06-30','yyyy-MM-dd')) AND dha.level05_parent_node = 'AC_F512000000'\n" +
" GROUP BY cost_category_level1\n" +
")\n" +
"SELECT cost_category_level1,\n" +
" base.curr_value AS curr_value,\n" +
" IF (period LIKE 'Quarterly',COALESCE((base.curr_value - base.prev_qtr_value),0) /COALESCE(ABS(base.prev_qtr_value),1)*100,'-') AS VPQ,\n" +
" IF (period IN ('Quarterly','YTD'),COALESCE((base.curr_value - base.prev_year_value),0) /COALESCE(ABS(base.prev_year_value),1)*100,'-') AS VPY\n" +
"FROM base\n" +
"WHERE (FLOOR(ABS(base.curr_value)) != 0)\n" +
"ORDER BY cost_category_level1";
sqlparser.parse( );
assertTrue(testScriptGenerator.verifyScript(EDbVendor.dbvdatabricks, sqlparser.sqlstatements.get(0).toString(), sqlparser.sqlstatements.get(0).toScript()));
}
public void test11( )
{
String sql = "SELECT\n" +
" 'TOTAL' AS Week,\n" +
" SUM(IF ((T2311468.usd_amount_ph IS NOT NULL AND (xdd.calendar_date BETWEEN to_date ('2022-01-01', 'yyyy-MM-dd') AND to_date ('2022-03-31', 'yyyy-MM-dd'))), T2311468.usd_amount_ph, 0)) AS current_year,\n" +
" SUM(IF ((T2311468.usd_amount_ph IS NOT NULL AND (xdd.calendar_date BETWEEN to_date ('2022-01-01', 'yyyy-MM-dd') -INTERVAL 1 YEAR AND to_date ('2022-03-31', 'yyyy-MM-dd') -INTERVAL 1 YEAR)), T2311468.usd_amount_ph, 0)) AS prev_year\n" +
"FROM\n" +
" table0 xdd\n" +
"INNER JOIN table1 T2311468 ON\n" +
" xdd.calendar_date = T2311468.payment_date_ph\n" +
"LEFT OUTER JOIN table2 T2305475\n" +
" ON\n" +
" T2305475.invoice_id = T2311468.invoice_id\n" +
" AND T2305475.ssn = T2311468.ssn\n" +
"INNER JOIN table3 xgcc\n" +
" ON\n" +
" xgcc.cci = T2311468.accts_pay_ccid_actual\n" +
" AND xgcc.ssn = T2311468.ssn\n" +
"WHERE\n" +
" 1=1";
TGSqlParser sqlparser = new TGSqlParser( EDbVendor.dbvdatabricks);
sqlparser.sqltext = sql;
sqlparser.parse( );
assertTrue(testScriptGenerator.verifyScript(EDbVendor.dbvdatabricks, sqlparser.sqlstatements.get(0).toString(), sqlparser.sqlstatements.get(0).toScript()));
}
public void test12( )
{
String sql = "SELECT ds_ge_supplier_normalized_name AS vendor_name,\n" +
" SUM(curr_value) AS dollar_value\n" +
" FROM (SELECT ds_ge_supplier_normalized_name AS ds_ge_supplier_normalized_name,\n" +
" SUM(PO_Remaining_amount) AS curr_value\n" +
" FROM (SELECT COALESCE(t4.usd_amount_ordered,0) -(COALESCE(t4.usd_amount_billed,0) +COALESCE(t4.usd_amount_cancelled,0)) AS PO_Remaining_amount,\n" +
" t0.creation_date,\n" +
" t7.ge_supplier_normalized_name AS ds_ge_supplier_normalized_name\n" +
" FROM ((((((((fdl_db_qa_dbr_consumption_inc.xx_po_headers_v t0\n" +
" JOIN fdl_db_qa_dbr_consumption_inc.xx_source_systems t1 ON ( (t0.source_system_name = t1.source_system_name)))\n" +
" JOIN fdl_db_qa_dbr_consumption_inc.xx_date_dim t2 ON ( (CAST (t0.creation_date AS DATE) = t2.calendar_date)))\n" +
" JOIN fdl_db_qa_dbr_consumption_inc.xx_po_lines_v t3\n" +
" ON ( (t0.po_header_id = t3.po_header_id\n" +
" AND t0.source_system_name = t3.source_system_name)))\n" +
" JOIN fdl_db_qa_dbr_consumption_inc.xx_po_distributions_v t4\n" +
" ON ( (t3.po_header_id = t4.po_header_id\n" +
" AND t3.po_line_id = t4.po_line_id\n" +
" AND t3.source_system_name = t4.source_system_name)))\n" +
" JOIN fdl_db_qa_dbr_consumption_inc.xx_organizations t5\n" +
" ON ( (t0.org_id = t5.organization_id\n" +
" AND t0.source_system_name = t5.source_system_name)))\n" +
" JOIN fdl_db_qa_dbr_consumption_inc.xx_hr_locations t6\n" +
" ON ( (t5.location_id = t6.location_id\n" +
" AND t5.source_system_name = t6.source_system_name)))\n" +
" LEFT JOIN fdl_db_qa_dbr_consumption_inc.xx_po_vendor_sites_v t7\n" +
" ON ( (t0.vendor_site_id = t7.vendor_site_id\n" +
" AND t0.source_system_name = t7.source_system_name)))\n" +
" JOIN fdl_db_qa_dbr_consumption_inc.xx_gl_code_combinations xgcc\n" +
" ON ( (t4.code_combination_id = xgcc.code_combination_id\n" +
" AND t4.source_system_name = xgcc.source_system_name)))\n" +
" LEFT JOIN fdl_db_qa_dbr_consumption_inc.xx_ap_terms t9\n" +
" ON ( (t0.terms_id = t9.term_id\n" +
" AND t0.source_system_name = t9.source_system_name))\n" +
" LEFT JOIN fdl_db_qa_dbr_consumption_inc.xx_po_vendor_sites_v t10 /* DIM_PO_VENDOR_SITES_V */\n" +
" ON t0.source_system_name = t10.source_system_name\n" +
" AND t0.vendor_site_id = t10.vendor_site_id\n" +
" WHERE t0.authorization_status IN ('02 - Active','03 - In release','05 - Release completed','APPROVED','IN PROCESS','PRE-APPROVED')\n" +
" AND t7.internal_external IN ('External','Unspecified')\n" +
" AND t0.closed_code = 'OPEN'\n" +
" AND t0.cancel_flag NOT IN ('Y')\n" +
" AND t3.cancel_flag NOT IN ('Y')\n" +
" AND (t5.attribute17 IS NULL OR t5.attribute17 != 'Buy Only')\n" +
" AND to_date(t0.creation_date,'YYYY-MM-DD') BETWEEN NOW() -INTERVAL '5' YEAR AND NOW()\n" +
" AND ((COALESCE(t4.usd_amount_ordered,0) -(COALESCE(t4.usd_amount_billed,0) +COALESCE(t4.usd_amount_cancelled,0))) != 0)\n" +
" AND t9.due_days < 30\n" +
" AND to_date(t0.creation_date,'YYYY-MM-DD') BETWEEN NOW() -INTERVAL '5' YEAR AND NOW()) s0\n" +
" GROUP BY s0.ds_ge_supplier_normalized_name\n" +
" ORDER BY curr_value DESC LIMIT 10)\n" +
" GROUP BY ds_ge_supplier_normalized_name\n" +
" ORDER BY dollar_value DESC";
TGSqlParser sqlparser = new TGSqlParser( EDbVendor.dbvdatabricks);
sqlparser.sqltext = sql;
int ret = sqlparser.parse( );
assertTrue(ret == 0);
assertTrue(testScriptGenerator.verifyScript(EDbVendor.dbvdatabricks, sqlparser.sqlstatements.get(0).toString(), sqlparser.sqlstatements.get(0).toScript()));
}
public void test13( )
{
String sql = "select * from t where to_date ('2023-06-30','yyyy-MM-dd') = CURRENT_DATE()";
TGSqlParser sqlparser = new TGSqlParser( EDbVendor.dbvdatabricks);
sqlparser.sqltext = sql;
int ret = sqlparser.parse( );
assertTrue(ret == 0);
assertTrue(testScriptGenerator.verifyScript(EDbVendor.dbvdatabricks, sqlparser.sqlstatements.get(0).toString(), sqlparser.sqlstatements.get(0).toScript()));
}
}