DuckDB CLI 및 SQL CheatSheet - 주요 파일 포맷, DB Attach, HTTP/S3, Excel DuckDB CLI 및 SQL CheatSheet 가이드 1. DuckDB와 CLI/SQL 기능 소개 DuckDB는 고성능 분석 쿼리를 위해 설계된 인-프로세스, 컬럼 지향 OLAP 데이터베이스입니다. CLI와 SQL 인터페이스를 통해 CSV, Parquet, JSON, Excel 등의 파일 포맷을 처리하고, 데이터베이스를 관리하며, HTTP/S3와 같은 웹 기반 스토리지와 통합할 수 있습니다. 이 치트시트는 DuckDB의 CLI와 SQL 명령어를 중심으로 실용적인 예제와 모범 사례를 제공합니다. 주요 기능: ...
jq 명령어를 통한 Json to CSV 변환 Jq 명령어 활용 https://stedolan.github.io/jq/{:target="_blank"} jq is a lightweight and flexible command-line JSON processor. jq를 사용하여 JSON 배열과 NDJSON 형식의 데이터를 CSV 형식으로 변환하는 예제 JSON 배열은 여러 개의 JSON 객체를 대괄호로 묶은 형식이고, NDJSON은 Newline Delimited JSON의 약자로, 한 줄에 하나의 JSON 객체를 나열한 형식 NDJSON은 MongoDB나 Elasticsearch 등에서 사용되는 데이터 형식 Json 배열에서 CSV 데이터 변환 [ { "fs": "/dev/mapper/vgubuntu-root", "type": "ext4", "size": "915G", "used": "135G", "avail": "734G", "usedpercentage": "16%", "mounted": "/" }, { "fs": "/dev/nvme0n1p2", "type": "ext4", "size": "1.4G", "used": "378M", "avail": "939M", "usedpercentage": "29%", "mounted": "/boot" }, { "fs": "/dev/nvme0n1p1", "type": "vfat", "size": "511M", "used": "30M", "avail": "482M", "usedpercentage": "6%", "mounted": "/boot/efi" } ] Header 추출 CSV 파일의 헤더(keys)를 추출하기 위해 다음과 같은 명령어를 사용 ...
CSV 파일을 SQLite3 Import 및 Query CSV to SQLite3 CSV 파일을 SQLite3로 Import 쿼리 CSV 샘플 데이터 확인 $ cat cities.csv "LatD", "LatM", "LatS", "NS", "LonD", "LonM", "LonS", "EW", "City", "State" 41, 5, 59, "N", 80, 39, 0, "W", "Youngstown", OH 42, 52, 48, "N", 97, 23, 23, "W", "Yankton", SD 46, 35, 59, "N", 120, 30, 36, "W", "Yakima", WA ... TIP: cat 대신 bat 사용하면 더 나은 뷰어를 제공 $ bat cities.csv CSV Import .mode csv : csv 모드로 전환 .import ./cities.csv ci : ./cities.csv 파일을 ci 테이블로 Import .import --skip 1 ./cities.csv ci : ./cities.csv 테이블이 존재 할때 header 제외 .import --csv ./cities.csv ci : ./cities.csv csv 모드 inline 옵션 # ci.db 생성 및 인터랙티브 모드 $ sqlite3 ci.db SQLite version 3.37.0 2021-12-09 01:34:53 Enter ".help" for usage hints. sqlite> .mode csv sqlite> .import ./cities.csv ci sqlite> .tables ci sqlite> select count(*) from ci; count(*) 129 sqlite> .mode list sqlite> .header on sqlite> select * from ci limit 10; LatD| "LatM"| "LatS"| "NS"| "LonD"| "LonM"| "LonS"| "EW"| "City"| "State" 41| 5| 59| "N"| 80| 39| 0| "W"| "Youngstown"| OH 42| 52| 48| "N"| 97| 23| 23| "W"| "Yankton"| SD 46| 35| 59| "N"| 120| 30| 36| "W"| "Yakima"| WA 42| 16| 12| "N"| 71| 48| 0| "W"| "Worcester"| MA 43| 37| 48| "N"| 89| 46| 11| "W"| "Wisconsin Dells"| WI 36| 5| 59| "N"| 80| 15| 0| "W"| "Winston-Salem"| NC 49| 52| 48| "N"| 97| 9| 0| "W"| "Winnipeg"| MB 39| 11| 23| "N"| 78| 9| 36| "W"| "Winchester"| VA 34| 14| 24| "N"| 77| 55| 11| "W"| "Wilmington"| NC 39| 45| 0| "N"| 75| 33| 0| "W"| "Wilmington"| DE Inline command $ sqlite3 /tmp/t.db ".import -csv ./cities.csv ci" "select * from ci" -header LatD| "LatM"| "LatS"| "NS"| "LonD"| "LonM"| "LonS"| "EW"| "City"| "State" 41| 5| 59| "N"| 80| 39| 0| "W"| "Youngstown"| OH 42| 52| 48| "N"| 97| 23| 23| "W"| "Yankton"| SD 46| 35| 59| "N"| 120| 30| 36| "W"| "Yakima"| WA 42| 16| 12| "N"| 71| 48| 0| "W"| "Worcester"| MA 43| 37| 48| "N"| 89| 46| 11| "W"| "Wisconsin Dells"| WI 36| 5| 59| "N"| 80| 15| 0| "W"| "Winston-Salem"| NC 49| 52| 48| "N"| 97| 9| 0| "W"| "Winnipeg"| MB 39| 11| 23| "N"| 78| 9| 36| "W"| "Winchester"| VA 34| 14| 24| "N"| 77| 55| 11| "W"| "Wilmington"| NC 39| 45| 0| "N"| 75| 33| 0| "W"| "Wilmington"| DE
MySQL 테이블에 Text 파일을 빠르게 Insert 하는 방벙 MySQL LOAD DATA https://dev.mysql.com/doc/refman/8.0/en/load-data.html{:target="_blank"} The LOAD DATA statement reads rows from a text file into a table at a very high speed LOAD DATA statement 사전 준비 : secure_file_priv secure_file_priv 의 설정값이 NULL 로 되어 있는데 빈문자열로 수정해야 함 SHOW VARIABLES LIKE 'secure_file_priv' -- NULL my.cnf 에서 수정 및 재시작 필요 [mysqld] secure-file-priv="" LOAD DATA 최소 설정 LOAD DATA INFILE '/path/data.txt' INTO TABLE data_table Field and Line Handling : Default 설정 LOAD DATA INFILE '/path/data.txt' INTO TABLE data_table FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' 헤더 라인 무시 LOAD DATA INFILE '/path/data.txt' INTO TABLE data_table IGNORE 1 LINES LOAD DATA 기본 설정 (CSV) LOAD DATA INFILE '/path/data.txt' IGNORE INTO TABLE data_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n' (filed1, filed2, filed3 ) Duplicate-Key and Error Handling IGNORE : unique key 값이 있으면 새로운 행은 버려짐 default REPLACE : unique key 값 기준으로 새로운 행으로 변경 필드 개수, 순서 기본적으로 파일의 구분자 기준 순서와 필드와 1:1 매치됨 필드 지정을 통해 순서 조정 가능 파일의 구분자 필드가 많으면 : 버려짐 테이블의 필드가 많으면 : 나머지 필드는 Default 값이나 NULL 처리 (NULL 허용시) Input Preprocessing 테이블의 중간 필드를 무시 (Default 값 or NULL 처리) 하려면 @dummy 키워드로 필드 지정 필드 가공은 SET 명령으로 처리 @변수 명으로 받아서 가공후 필드에 지정할 수 있음 LOAD DATA INFILE '/path/data.txt' IGNORE INTO TABLE data_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (filed1, @dummy, filed3, @var1, filed5 ) SET filed2 = CURRENT_TIMESTAMP, filed4 = @var1 * 2; Index Handling : LOAD DATA 문의 성능향상을 위한 Index check option unique index checks option : SET unique_checks = 0 SET unique_checks = 1 foreign key check option : SET foreign_key_checks = 0 SET foreign_key_checks = 1 로컬(클라이언트) 데이터 원격 INSERT https://dev.mysql.com/doc/refman/8.0/en/load-data-local-security.html{:target="_blank"} 사전 준비 : local_infile 설정 (server and client) server : local_infile 의 설정값이 ON(1) 로 수정해야 함 client : 접속시 --local-infile 옵션 추가 SHOW VARIABLES LIKE 'local_infile' ; -- OFF SET GLOBAL local_infile = 1 ; SHOW VARIABLES LIKE 'local_infile' ; -- ON LOCAL PATH 지정 LOAD DATA LOCAL INFILE ... LOAD DATA LOCAL INFILE '/path/data.txt' INTO TABLE data_table