Hướng dẫn sử dụng Google Sheets để tải các file CSV phức tạp vào BigQuery

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 while
parsing row starting at position: 104942. Error: Missing close
double 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:

1. Chọn một tập dữ liệu và nhấn Create Table
2. Chọn Drive làm nguồn, nhập URL cho trang tính
3. Đặt Google Sheet làm định dạng tập tin (file format)
4. Đặt tên cho bảng. Ở đây là airbnb_raw_googlesheet.
5. 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:integer

Bả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 AS
SELECT * 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