GBase 8a Data Import & Export Guide: gload, LOAD DATA, and SELECT INTO OUTFILE

# load_orders.cfg
host     = 10.168.10.26
port     = 5258
user     = gbase
password = your_password
database = sales_db
table    = orders

# Data files (wildcards supported to load multiple files at once)
infile   = /data/orders/orders_2024_*.csv

# File format
fields terminated by ','       # field delimiter
enclosed by '"'                 # string quoting
lines terminated by '\n'        # line terminator
ignore 1 lines                  # skip header line

# Column mapping (file columns mapped to table columns in order)
(order_id, customer_id, dept_id, amount, status, order_date, create_time)

# Error handling
errors = 1000                   # max bad rows allowed (exceeding aborts the load)

gload -f load_orders.cfg

character_set = utf8    # encoding of the data file

SELECT task_id, error_row_no, error_msg,
       SUBSTR(raw_data, 1, 200) AS raw_line
FROM gclusterdb.load_error_log
WHERE task_id = '20240601_143022_000001'
LIMIT 50;

LOAD DATA INFILE '/data/orders/orders.csv'
INTO TABLE orders
CHARACTER SET utf8
FIELDS TERMINATED BY ','
       ENCLOSED BY '"'
       ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(order_id, customer_id, dept_id, amount, status, order_date, @create_time)
SET create_time = STR_TO_DATE(@create_time, '%Y-%m-%d %H:%i:%s');

LOAD DATA LOCAL INFILE '/local/path/data.csv'
INTO TABLE orders
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

SELECT order_id, customer_id, amount, order_date
INTO OUTFILE '/data/export/orders_2024.csv'
CHARACTER SET utf8
FIELDS TERMINATED BY ','
       ENCLOSED BY '"'
       ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM orders
WHERE order_date >= '2024-01-01';

SELECT *
INTO OUTFILE '/data/export/orders_2024q1.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM orders PARTITION (p2024q1);

#!/bin/bash
for month in 01 02 03 04 05 06 07 08 09 10 11 12; do
    gccli -u gbase -p password -e "
        SELECT * INTO OUTFILE '/data/export/orders_2024${month}.csv'
        FIELDS TERMINATED BY ','
        LINES TERMINATED BY '\n'
        FROM orders
        WHERE order_date BETWEEN '2024-${month}-01' AND LAST_DAY('2024-${month}-01')
    " &
done
wait
echo "All exports done"

-- Running exports
SELECT task_id, table_name, status, start_time,
       exported_rows,
       TIMESTAMPDIFF(SECOND, start_time, NOW()) AS elapsed_sec
FROM gclusterdb.export_task
WHERE status = 'RUNNING';

-- Historical exports
SELECT task_id, table_name, status, exported_rows,
       start_time, end_time
FROM gclusterdb.export_task
ORDER BY start_time DESC
LIMIT 20;

LOAD DATA INFILE '/data/orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ','
(@order_id, @customer_id, @amount, @order_date_str)
SET
    order_id    = @order_id,
    customer_id = @customer_id,
    amount      = @amount,
    order_date  = STR_TO_DATE(@order_date_str, '%Y%m%d');