adw tpcds

nigelbayliss · nigelbayliss · commit 51a708fddf3f · 2019-05-14T12:40:30.000+01:00
diff --git a/optimizer/tpcds_etabs/README.md b/optimizer/tpcds_etabs/README.md
@@ -2,6 +2,8 @@
 
 Run the tpcds_makex.sql in your TPC-DS schema at it will spool a new script called tpcds_etabs.sql. This spooled script will create a bunch of external tables enabling you to load the TPC-DS files generated by dsdgen.
 
+I have also included tpcds_makex_adw.sql. It will create external tables in ADW to load from the object store.
+
 The example tpcds_etabs.sql script has been tested on TPC-DS v1.4. For other versions, just run tpcds_makex.sql to create a new tpcds_etabs.sql file. The whole workflow has been tested on v1.4 and v2.5.
 
 If you leave the script unedited, you just create a symbolic link "/tmp/tpcdsload" pointing to the directory containing all of your dsdgen files.
diff --git a/optimizer/tpcds_etabs/tpcds_makex_adw.sql b/optimizer/tpcds_etabs/tpcds_makex_adw.sql
@@ -0,0 +1,63 @@
+set serveroutput on
+set feedback off
+set echo off
+set termout off
+
+spool tpcds_etabs.sql
+
+declare
+  sep char(1);
+  datat user_tab_columns.data_type%type;
+begin
+  dbms_output.put_line('create or replace directory tpcsd_load_dir as ''/tmp/tpcdsload'';');
+  for tabl in (select table_name,lower(table_name) ltnam from user_tables where table_name not in (select table_name from user_external_tables) and substr(table_name,1,2) <> 'S_' order by 1)
+  loop
+     dbms_output.put_line('-------------------------------------------------------------------------------');
+     dbms_output.put_line('drop table X_'||tabl.table_name||';');
+     dbms_output.put_line('begin');
+     dbms_output.put_line('dbms_cloud.create_external_table(''X_'||tabl.table_name||''', credential_name=>''DEF_CRED_NAME'',');
+     dbms_output.put_line('file_uri_list =>''https://objectstorage.YOUR_REGION.oraclecloud.com/n/CONTAINER/b/BUCKET/o/'||tabl.ltnam||'.dat'',');
+     dbms_output.put_line('format => json_object(''delimiter'' value ''|''),');
+     dbms_output.put_line('column_list => ''');
+     sep := ' ';
+     for tabc in (select * from user_tab_columns where table_name = tabl.table_name order by column_id)
+     loop
+        datat := tabc.data_type;
+        if (datat = 'CHAR' or datat = 'VARCHAR2')
+        then
+           datat := datat||'('||tabc.data_length||')';
+        end if;
+        if (datat = 'NUMBER')
+        then
+           if (tabc.data_precision is null)
+           then
+              datat := datat||'(38)';
+           else
+              datat := datat||'('||tabc.data_precision||','||tabc.data_scale||')';
+           end if;
+        end if;
+        dbms_output.put_line(sep||tabc.column_name||' '||datat);
+        sep := ',';
+     end loop;
+     dbms_output.put_line(''',');
+     sep := ' ';
+     dbms_output.put_line('field_list => ''');
+     for tabc in (select * from user_tab_columns where table_name = tabl.table_name order by column_id)
+     loop
+        if (tabc.data_type = 'DATE')
+        then
+          dbms_output.put_line(sep||tabc.column_name||' date "YYYY-MM-DD"');
+        else
+          dbms_output.put_line(sep||tabc.column_name);
+        end if;
+        sep := ',';
+     end loop;
+     dbms_output.put_line('''');
+     dbms_output.put_line(');');
+     dbms_output.put_line('end;');
+     dbms_output.put_line('/');
+  end loop;
+end;
+/
+
+spool off
