![]() \copy customer from '/home/postgres/TPCHDATA/customer_7.tbl' WITH DELIMITER AS '|' \copy customer from '/home/postgres/TPCHDATA/customer_6.tbl' WITH DELIMITER AS '|' \copy customer from '/home/postgres/TPCHDATA/customer_5.tbl' WITH DELIMITER AS '|' \copy customer from '/home/postgres/TPCHDATA/customer_4.tbl' WITH DELIMITER AS '|' \copy customer from '/home/postgres/TPCHDATA/customer_3.tbl' WITH DELIMITER AS '|' \copy customer from '/home/postgres/TPCHDATA/customer_2.tbl' WITH DELIMITER AS '|' For PostgreSQL make a file with the copy commands for all tables for example: \copy customer from '/home/postgres/TPCHDATA/customer_1.tbl' WITH DELIMITER AS '|' ![]() Query OK, 150000 rows affected (1.56 sec)īoth PostgreSQL and Amazon Redshift use the copy command to bulk load data, however Redshift has additional requirements to load the data into the cloud. Records: 150000 Deleted: 0 Skipped: 0 Warnings: 0 Query OK, 150000 rows affected (0.74 sec) Load data infile '/home/mysql/TPCHDATA/customer_6.tbl' INTO table CUSTOMER fields terminated by '|' Īnd run as follows: mysql> source /home/mysql/TPCHDATA/loadfiles.sql Load data infile '/home/mysql/TPCHDATA/customer_5.tbl' INTO table CUSTOMER fields terminated by '|' Load data infile '/home/mysql/TPCHDATA/customer_4.tbl' INTO table CUSTOMER fields terminated by '|' Load data infile '/home/mysql/TPCHDATA/customer_3.tbl' INTO table CUSTOMER fields terminated by '|' Load data infile '/home/mysql/TPCHDATA/customer_2.tbl' INTO table CUSTOMER fields terminated by '|' Set L_SHIPDATE = '%Y-%b-%d'),L_COMMITDATE = '%Y-%b-%d'),L_RECEIPTDATE = '%Y-%b-%d') Īnd a SET command as follows shown for the ORDERS table: load data infile '/home/mysql/TPCHDATA/orders_1.tbl' INTO table ORDERS fields terminated by O_ORDERKEY, O_CUSTKEY, O_ORDERPRIORITY, O_SHIPPRIORITY, O_CLERK, O_ORDERSTATUS, O_TOTALPRICE, O_COMMENT)Īdd these commands to a file: load data infile '/home/mysql/TPCHDATA/customer_1.tbl' INTO table CUSTOMER fields terminated by '|' Load data infile does not offer enable a method by which different date formats can be specified however this can be achieved by specifying an additional SET command as shown for the LINEITEM table: load data infile '/home/mysql/TPCHDATA/lineitem_1.tbl' INTO table LINEITEM fields terminated by L_ORDERKEY, L_DISCOUNT, L_EXTENDEDPRICE, L_SUPPKEY, L_QUANTITY, L_RETURNFLAG, L_PARTKEY, L_LINESTATUS, L_TAX, L_SHIPMODE, L_LINENUMBER, L_SHIPINSTRUCT, L_COMMENT) mysql> load data infile '/home/mysql/TPCHDATA/supplier_1.tbl' INTO table SUPPLIER fields terminated by '|' sqlldr tpch/tpch control=/home/oracle/sqlldr-orders.ctl direct=trueĪs with MySQL bulk loading is done in MariaDB using the load data infile command. Now run SQL*Loader specifying the control file and username and password. (L_SHIPDATE DATE "yyyy-mon-dd",L_ORDERKEY,L_DISCOUNT ,L_EXTENDEDPRICE,L_SUPPKEY,L_QUANTITY,L_RETURNFLAG,L_PARTKEY,L_LINESTATUS,L_TAX,L_COMMITDATE DATE "yyyy-mon-dd", L_RECEIPTDATE DATE "yyyy-mon-dd",L_SHIPMODE, L_LINENUMBER, L_SHIPINSTRUCT, L_COMMENT) Infile '/home/oracle/TPCHDATA/lineitem_*.tbl' (O_ID,O_W_ID,O_D_ID,O_C_ID,O_CARRIER_ID,O_OL_CNT,O_ALL_LOCAL,O_ENTRY_D DATE "YYYĪ further different date formatting example can be seen for the LINEITEM table in the TPCH schema. Infile '/home/oracle/TPCCDATA/orders_*.tbl' Also note how the DATE format has been specified. Firstly note that the control can accept wildcard characters and therefore multiple files can be loaded with one command. The following example shows a control file for the ORDERS table from the TPCH schema. To use SQL*Loader firstly create a control file. SQL*Loader has the advantage of flexibility in being adaptable to loading data in many different formats. SQL*Loader is the default method for loading Oracle with external data.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |