Skip to content

Instantly share code, notes, and snippets.

@pavel242242

pavel242242/heatwave_notes.txt

Last active June 19, 2024 11:32
Show Gist options
  • Save pavel242242/be4adfff2615d246df154b81c68634dc to your computer and use it in GitHub Desktop.
Save pavel242242/be4adfff2615d246df154b81c68634dc to your computer and use it in GitHub Desktop.
Download ZIP
Raw
heatwave_notes.txt
### LOAD via datalake (plus cca 5 sec analyze overhead per table)
| SCHEMA TABLE TABLE IS RAW NUM. OF ESTIMATED SUMMARY OF |
| NAME NAME CREATED FILE SIZE COLUMNS ROW COUNT ISSUES |
| ------ ----- -------- --------- ------- --------- ---------- |
| `keboola` `LINEITEM` NO 6.87 GiB 16 45.05 M |
| TOTAL ESTIMATED ESTIMATED TOTAL DICTIONARY VARLEN ESTIMATED |
| SCHEMA OFFLOADABLE HEATWAVE NODE MYSQL NODE STRING ENCODED ENCODED LOAD |
| NAME TABLES FOOTPRINT FOOTPRINT COLUMNS COLUMNS COLUMNS TIME |
| ------ ----------- --------- --------- ------- ---------- ------- --------- |
| `keboola` 1 3.00 GiB 1.06 MiB 5 0 5 1.18 min |
CREATE TABLE `keboola`.`LINEITEM`( `L_ORDERKEY` int unsigned NOT NULL, `L_PARTKEY` mediumint unsigned NOT NULL, `L_SUPPKEY` mediumint unsigned NOT NULL, `L_LINENUMBER` tinyint unsigned NOT NULL, `L_QUANTITY` decimal(4,2) NOT NULL, `L_EXTENDEDPRICE` decimal(8,2) NOT NULL, `L_DISCOUNT` decimal(3,2) NOT NULL, `L_TAX` decimal(3,2) NOT NULL, `L_RETURNFLAG` varchar(1) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=VARLEN', `L_LINESTATUS` varchar(1) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=VARLEN', `L_SHIPDATE` date NOT NULL, `L_COMMITDATE` date NOT NULL, `L_RECEIPTDATE` date NOT NULL, `L_SHIPINSTRUCT` varchar(17) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=VARLEN', `L_SHIPMODE` varchar(7) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=VARLEN', `L_COMMENT` varchar(43) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=VARLEN') ENGINE=lakehouse SECONDARY_ENGINE=RAPID ENGINE_ATTRIBUTE='{"file": [{"par": "https://objectstorage.eu-frankfurt-1.oraclecloud.com/p/H3UgoLVLUY9p1_IILU7EHcAHF1TnclrUf7PyoRSytn5j0Cr1QmawZqmQu8ZXS3UN/n/frx6pux5wrxh/b/bucket-20240610-1008/o/LINEITEM.csv"}], "dialect": {"format": "csv", "has_header": true, "field_delimiter": ",", "record_delimiter": "\\n"}}';
Query OK, 0 rows affected (0.0147 sec)
MySQL 10.0.1.69:3306 ssl keboola SQL > ALTER TABLE /*+ AUTOPILOT_DISABLE_CHECK */ `keboola`.`LINEITEM` SECONDARY_LOAD;
Query OK, 0 rows affected (36.9520 sec)
6.87 GiB
ALTER TABLE /*+ AUTOPILOT_DISABLE_CHECK */ `keboola`.`CUSTOMER` SECONDARY_LOAD;
Query OK, 0 rows affected (14.7839 sec)
almost zero rows:
ALTER TABLE /*+ AUTOPILOT_DISABLE_CHECK */ `keboola`.`NATION` SECONDARY_LOAD;
Query OK, 0 rows affected (4.5365 sec) + 5 sec overhead
PARTSUPP + ORDERS (18.8049 sec)
all in one command
+-------------------------------------------------------------------------------+
| LOAD SUMMARY |
+-------------------------------------------------------------------------------+
| |
| SCHEMA TABLES TABLES COLUMNS LOAD |
| NAME LOADED FAILED LOADED DURATION |
| ------ ------ ------ ------- -------- |
| `keboola` 8 0 61 1.85 min |
| |
+-------------------------------------------------------------------------------+
detail:
+----------------------------------------+
| TABLE LOAD |
+----------------------------------------+
| TABLE (1 of 8): `keboola`.`CUSTOMER` |
| Commands executed successfully: 2 of 2 |
| Warnings encountered: 0 |
| Table load succeeded! |
| Total columns loaded: 8 |
| Elapsed time: 12.02 s |
| |
+----------------------------------------+
7 rows in set (0.0104 sec)
+----------------------------------------+
| TABLE LOAD |
+----------------------------------------+
| TABLE (2 of 8): `keboola`.`LINEITEM` |
| Commands executed successfully: 2 of 2 |
| Warnings encountered: 0 |
| Table load succeeded! |
| Total columns loaded: 16 |
| Elapsed time: 38.30 s |
| |
+----------------------------------------+
7 rows in set (0.0104 sec)
+----------------------------------------+
| TABLE LOAD |
+----------------------------------------+
| TABLE (3 of 8): `keboola`.`NATION` |
| Commands executed successfully: 2 of 2 |
| Warnings encountered: 0 |
| Table load succeeded! |
| Total columns loaded: 4 |
| Elapsed time: 3.48 s |
| |
+----------------------------------------+
7 rows in set (0.0104 sec)
+----------------------------------------+
| TABLE LOAD |
+----------------------------------------+
| TABLE (4 of 8): `keboola`.`ORDERS` |
| Commands executed successfully: 2 of 2 |
| Warnings encountered: 0 |
| Table load succeeded! |
| Total columns loaded: 9 |
| Elapsed time: 17.59 s |
| |
+----------------------------------------+
7 rows in set (0.0104 sec)
+----------------------------------------+
| TABLE LOAD |
+----------------------------------------+
| TABLE (5 of 8): `keboola`.`PART` |
| Commands executed successfully: 2 of 2 |
| Warnings encountered: 0 |
| Table load succeeded! |
| Total columns loaded: 9 |
| Elapsed time: 14.17 s |
| |
+----------------------------------------+
7 rows in set (0.0104 sec)
+----------------------------------------+
| TABLE LOAD |
+----------------------------------------+
| TABLE (6 of 8): `keboola`.`PARTSUPP` |
| Commands executed successfully: 2 of 2 |
| Warnings encountered: 0 |
| Table load succeeded! |
| Total columns loaded: 5 |
| Elapsed time: 16.63 s |
| |
+----------------------------------------+
7 rows in set (0.0104 sec)
+----------------------------------------+
| TABLE LOAD |
+----------------------------------------+
| TABLE (7 of 8): `keboola`.`REGION` |
| Commands executed successfully: 2 of 2 |
| Warnings encountered: 0 |
| Table load succeeded! |
| Total columns loaded: 3 |
| Elapsed time: 3.64 s |
| |
+----------------------------------------+
7 rows in set (0.0104 sec)
+----------------------------------------+
| TABLE LOAD |
+----------------------------------------+
| TABLE (8 of 8): `keboola`.`SUPPLIER` |
| Commands executed successfully: 2 of 2 |
| Warnings encountered: 0 |
| Table load succeeded! |
| Total columns loaded: 7 |
| Elapsed time: 5.12 s |
| |
+----------------------------------------+
yelp:
csv: \\n vs \\r\\n
ERROR: 1074 (42000): Column length too big for column 'business_id' (max = 16383); use BLOB or TEXT instead
ERROR: 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
| TABLE (1 of 1): `keboola`.`YELP` |
| Commands executed successfully: 1 of 2 |
| Warnings encountered: 0 |
| Table load failed! |
| Command failed: "ALTER TABLE /*+ AUTOPILOT_DISABLE_CHECK */ `keboola`.`YELP` SECONDARY_LOAD;" |
| Error: Unable to load table from external source: Column 5 in {object: "yelp_academic_dataset_business.csv" in namespace: "frx6pux5wrxh", bucket: "bucket-20240610-1008"}:66890848: String is too long. |
| Elapsed time: 6.86 s |
| |
UPDATE YELP_JSON
-> SET attributes = REPLACE(REPLACE(REPLACE(attributes, '''', '"'), 'None', 'null'), 'True', 'true')
-> WHERE JSON_VALID(REPLACE(REPLACE(REPLACE(attributes, '''', '"'), 'None', 'null'), 'True', 'true')) = 1;
ERROR: 1288 (HY000): The target table YELP_JSON of the query is not updatable
AVRO:
ERROR: 3877 (HY000): Unable to load table from external source
note from hunka:
- mysqlsh overhead
- small table overhead
- updates (ERROR: 1288 (HY000): The target table NATION of the query is not updatable)
- replication with us oracle (shared credentials with oracle and support)
-
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment