BigQuery

1CREATE OR REPLACE TABLE test.sales_data (
2    order_id      INT64,
3    customer_name STRING,
4    order_date    DATE,
5    amount        NUMERIC(10,2),
6    country       STRING
7)
8PARTITION BY  order_date
9
10
11INSERT INTO test.sales_data (order_id, customer_name, order_date, amount, country) VALUES
12(1, 'Alice', '2025-04-08', 99.99, 'USA'),
13(2, 'Bob', '2025-04-08', 149.50, 'Canada'),
14(3, 'Charlie', '2025-04-09', 75.00, 'UK'),
15(4, 'Diana', '2025-04-10', 200.00, 'Australia');

1CREATE TABLE `sales_data` (
2  order_id      INT,
3  order_date    DATE NOT NULL,
4  customer_name VARCHAR(128),
5  amount        DECIMAL(10,2),
6  country       VARCHAR(48)
7) ENGINE=OLAP
8UNIQUE KEY(`order_id`,`order_date`)
9PARTITION BY RANGE(`order_date`) (
10PARTITION p20250408 VALUES [('2025-04-08'), ('2025-04-09')),
11PARTITION p20250409 VALUES [('2025-04-09'), ('2025-04-10')),
12PARTITION p20250410 VALUES [('2025-04-10'), ('2025-04-11'))
13)
14DISTRIBUTED BY HASH(`order_id`) BUCKETS 16
15PROPERTIES (
16 "dynamic_partition.enable" = "true",
17 "dynamic_partition.time_unit" = "DAY",
18 "dynamic_partition.end" = "5",
19 "dynamic_partition.prefix" = "p",
20 "dynamic_partition.buckets" = "16",
21 "replication_num" = "1"
22);

1EXPORT DATA
2OPTIONS (
3 uri = 'gs://mybucket/export/sales_data/*.parquet',
4 format = 'PARQUET')
5AS (
6SELECT *
7FROM test.sales_data 
8);

1LOAD LABEL sales_data_2025_04_08
2(
3 DATA INFILE("s3://mybucket/export/sales_data/000000000000.parquet")
4 INTO TABLE sales_data
5 FORMAT AS "parquet"
6 (order_id, order_date, customer_name, amount, country)
7)
8WITH S3
9(
10 "provider" = "GCP",
11 "s3.endpoint" = "storage.asia-southeast1.rep.googleapis.com",  
12 "s3.region" = "asia-southeast1",
13 "s3.access_key" = "<ak>",
14 "s3.secret_key" = "<sk>"
15);

1mysql> show load where label = "label_sales_data_2025_04_08"\G
2*************************** 1. row ***************************
3     JobId: 17956078
4     Label: label_sales_data_2025_04_08
5     State: FINISHED
6   Progress: 100.00% (1/1)
7       Type: BROKER
8   EtlInfo: unselected.rows=0; dpp.abnorm.ALL=0; dpp.norm.ALL=2
9   TaskInfo: cluster:storage.asia-southeast1.rep.googleapis.com; timeout(s):3600; max_filter_ratio:0.0; priority:NORMAL
10   ErrorMsg: NULL
11 CreateTime: 2025-04-10 17:50:53
12EtlStartTime: 2025-04-10 17:50:54
13EtlFinishTime: 2025-04-10 17:50:54
14LoadStartTime: 2025-04-10 17:50:54
15LoadFinishTime: 2025-04-10 17:50:54
16       URL: NULL
17 JobDetails: {"Unfinished backends":{"5eec1be8612d4872-91040ff1e7208a4f":[]},"ScannedRows":2,"TaskNumber":1,"LoadBytes":91,"All backends":{"5eec1be8612d4872-91040ff1e7208a4f":[10022]},"FileNumber":1,"FileSize":1620}
18TransactionId: 766228
19ErrorTablets: {}
20       User: root
21   Comment: 
221 row in set (0.00 sec)

1mysql> show load where state='CANCELLED' and label like "label_test%"\G
2*************************** 1. row ***************************
3     JobId: 18312384
4     Label: label_test123
5     State: CANCELLED
6   Progress: 100.00% (3/3)
7       Type: BROKER
8   EtlInfo: unselected.rows=0; dpp.abnorm.ALL=4; dpp.norm.ALL=0
9   TaskInfo: cluster:storage.asia-southeast1.rep.googleapis.com; timeout(s):14400; max_filter_ratio:0.0; priority:NORMAL
10   ErrorMsg: type:ETL_QUALITY_UNSATISFIED; msg:quality not good enough to cancel
11 CreateTime: 2025-04-15 17:32:59
12EtlStartTime: 2025-04-15 17:33:02
13EtlFinishTime: 2025-04-15 17:33:02
14LoadStartTime: 2025-04-15 17:33:02
15LoadFinishTime: 2025-04-15 17:33:02
16       URL: http://10.16.10.6:28747/api/_load_error_log?file=__shard_2/error_log_insert_stmt_7602ccd7c3a4854-95307efca7bfe342_7602ccd7c3a4854_95307efca7bfe342
17 JobDetails: {"Unfinished backends":{"7602ccd7c3a4854-95307efca7bfe341":[]},"ScannedRows":4,"TaskNumber":1,"LoadBytes":188,"All backends":{"7602ccd7c3a4854-95307efca7bfe341":[10022]},"FileNumber":3,"FileSize":4839}
18TransactionId: 769213
19ErrorTablets: {}
20       User: root
21   Comment: 

1[root@VM-10-6-centos ~]$ curl "http://10.16.10.6:28747/api/_load_error_log?file=__shard_2/error_log_insert_stmt_7602ccd7c3a4854-95307efca7bfe342_7602ccd7c3a4854_95307efca7bfe342"
2Reason: column_name[country], the length of input is too long than schema. first 32 bytes of input str: [USA] schema length: 1; actual length: 3; . src line []; 
3Reason: column_name[country], the length of input is too long than schema. first 32 bytes of input str: [Canada] schema length: 1; actual length: 6; . src line []; 
4Reason: column_name[country], the length of input is too long than schema. first 32 bytes of input str: [UK] schema length: 1; actual length: 2; . src line []; 
5Reason: column_name[country], the length of input is too long than schema. first 32 bytes of input str: [Australia] schema length: 1; actual length: 9; . src line [];