Việc đọc các tập tin CSV vào BigQuery bằng cách xây dựng đường ống ELT với Google Sheet làm trung gian cho phép bạn xử lý các tập tin CSV với nhiều dòng mới và các ký tự đặc biệt trong các cột. Hãy thử ngay theo các hướng dẫn dưới đây!
Xây dựng đường ống ELT bằng Google Sheets làm trung gian
BigQuery cung cấp khả năng đọc nhanh một tệp CSV từ giao diện người dùng và bằng dòng lệnh:
bq load --source_format CSV --autodetect \mydataset.mytable ./myfile.csv0
Hạn chế của phát hiện và đọc tự động
Cách làm này phù hợp với các tập tin CSV đơn giản, nhưng có thể không thành công trên các tệp CSV phức tạp. Để lấy ví dụ về một tập tin CSV bị lỗi, hãy xem xét tập dữ liệu cho thuê của New York City Airbnb từ Kaggle. Tập dữ liệu này có 16 cột, nhưng một trong số các cột bao gồm khá nhiều văn bản dạng tự do, có thể chứa cả các biểu tượng cảm xúc, ký tự xuống dòng,…
Thử mở tập tin này bằng BigQuery:
bq load --source_format CSV --skip_leading_rows 1 \advdata.airbnb_nyc_2019 AB_NYC_2019.csv \id:string,name:string,host_id:string,host_name:string,neighbourhood_group:string,neighbourhood:string,latitude:float,longitude:float,room_type:string,price:float,minimum_nights:integer,number_of_reviews:integer,last_review:string,reviews_per_month:float,calculated_host_listings_count:integer,availability_365:integer
Ta sẽ nhận được thông báo lỗi:
Error while reading data, error message: Error detected whileparsing row starting at position: 104942. Error: Missing closedouble quote (") character.
Đó là bởi vì một dòng dữ liệu (row) nằm trên nhiều dòng văn bản (line) và do đó, câu trích dẫn bắt đầu trên một dòng không thể được đóng. Đây không phải là một vấn đề dễ giải quyết. Rất nhiều công cụ gặp khó khăn với các tập tin CSV có nhiều dòng bên trong một ô dữ liệu.
Cách giải quyết của Sheets
Google Sheets có cơ chế đọc CSV tốt hơn nhiều. Bạn có thể mở một trang Google Sheet và đọc một tập tin CSV:
Điều thú vị là khi sử dụng Google Sheet, bạn có thể chuẩn bị dữ liệu tương tác trong Sheet trước khi tải nó vào BigQuery.
Việc đầu tiên là xóa hàng đầu (header) trong trang tính vì đó là dữ liệu không cần thiết.
ELT từ Google Sheet
Khi đã có trong Google Sheet, chúng ta có thể sử dụng một mẹo nhỏ hữu ích – BigQuery để có thể truy vấn trực tiếp các trang tính. Để làm được điều đó, ta cần khai báo Google Sheet như một bảng trong BigQuery:
Các bước thực hiện trên giao diện BigQuery:
- Chọn một tập dữ liệu và nhấn Create Table
- Chọn Drive làm nguồn, nhập URL cho trang tính
- Đặt Google Sheet làm định dạng tập tin (file format)
- Đặt tên cho bảng. Ở đây là
airbnb_raw_googlesheet. - Cụ thể hóa lược đồ:
id:string,name:string,host_id:string,host_name:string,neighbourhood_group:string,neighbourhood:string,latitude:float,longitude:float,room_type:string,price:float,minimum_nights:integer,number_of_reviews:integer,last_review:string,reviews_per_month:float,calculated_host_listings_count:integer,availability_365:integerBảng này không sao chép dữ liệu từ trang tính, mà truy vấn trực tiếp trên trang tính.
Cuối cùng, sao chép dữ liệu hiện có vào BigQuery (tất nhiên chúng ta cũng có thể thực hiện một số chuyển đổi ở đây):
CREATE OR REPLACE TABLE advdata.airbnb_nyc_2019 ASSELECT * FROM advdata.airbnb_raw_googlesheet
Tự động hóa quy trình
Bạn có thể tự động hóa quy trình theo các bước sau:
1. Sử dụng Python để đọc một tập tin CSV vào Sheets
2. Sử dụng tập lệnh dataform.co hoặc mã nguồn BigQuery để xác định bảng BigQuery và thực hiện ELT
Đã xong! Giờ đây, bạn đã có thể dễ dàng thao tác với các tập tin CSV trong BigQuery với đường ống ELT với Google Sheet làm trung gian.
Nguồn: Google Cloud Blog