Skip to main content

Google BigQuery

info

Ở mọi HDSD liên quan đến từng destination cụ thể sẽ luôn có 2 phần chính sau:

  1. Hướng dẫn cài đặt nền - setup trên dịch vụ mà bạn đã chọn
  2. Hướng dẫn setup destination và tạo connection từ dịch vụ bạn đã chọn trên Elton

Hướng dẫn set up Google BigQuery

Phần 1: Hướng dẫn cài đặt nền (setup trên Google Bigquery)​

Bước 1: Tạo tài khoản Google Cloud​

Nếu bạn chưa có một tài khoản Google Cloud, hãy đăng ký tại Google Cloud Console.

tip

Bạn nên sử dụng tài khoản Google Workspace nếu doanh nghiệp của bạn có dùng Workspace, như vậy dự án Google Cloud và BigQuery sẽ nằm trong quyền quản trị theo cấp doanh nghiệp. Nếu bạn dùng tài khoản cá nhân để tạo dự án Google Cloud, nó sẽ nằm ở tài khoản cá nhân đó và khi cần di chuyển sang tài khoản doanh nghiệp sẽ phức tạp.

Bước 2: Tạo dự án​

Sau khi bạn đã đăng nhập vào tài khoản Google Cloud, tạo một dự án mới hoặc chọn một dự án hiện có để làm việc với BigQuery.

tip

Bạn nên đặt tên dự án có dạng <tên công ty viết liền>-data-platform, tránh trường hợp đặt tên lung tung khó quản lý về sau.

Ví dụ: Công ty tên là Elton, thì đặt tên dự án là elton-data-platform.

Bước 3: Truy cập vào giao diện của Google BigQuery​

Nếu bạn truy cập thành công vào BigQuery Studio và có thể xem được giao diện của BigQuery, là bạn đã tạo thành công

Bước 4: Tạo service account để cho phép Elton Data truy cập vào Google BigQuery​

info

Service Account là một tài khoản đặc biệt, được Google thiết kế để cho phép các ứng dụng và dịch vụ bên ngoài có thể truy cập vào các dịch vụ của Google Cloud, bao gồm cả Google BigQuery. Elton Data dùng Service Account do bạn cung cấp để thực hiện việc ghi dữ liệu vào Google BigQuery.

Để xác thực cho Service Account, thay vì sử dụng mật khẩu vốn là một giải pháp kém an toàn hơn, Google Cloud sử dụng 1 file khóa JSON để xác thực. Bạn sẽ được hướng dẫn tạo file khóa JSON ở bên dưới.

Dưới đây là các bước để tạo một Service Account trên Google Cloud Platform (GCP) bằng cách sử dụng giao diện dòng lệnh và giao diện web của GCP.

4.1: Truy cập GCP Console​

4.2: Chọn Dự án​

  • Chọn dự án mà bạn đã dùng để kích hoạt Google BigQuery ở trên

4.3: Tạo Service Account​

4.3.1. Từ bảng điều khiển chính, chọn "IAM & Admin" từ thanh điều hướng bên trái > chọn "Service Accounts" trong danh sách con, hoặc sử dụng trực tiếp IAM Service Account để truy cập

4.3.2. Nhấn vào nút "Create Service Account" để tạo tài khoản Service Account mới

4.3.3. Điền tên cho Service Account của bạn > Nhấn Create and Continue

4.3.4 bước này, bạn cần điền 2 vai trò (role) cho Service Account. Elton cần 2 quyền BigQuery Admin và Storage Admin để luồng dữ liệu có thể hoạt động.

4.4: Tải xuống Khóa JSON​

4.4.1. Ở dòng Service Account bạn vừa tạo từ danh sách "Service Account", ở cột Action, có nút ba chấm. Nhấn vào nút ba chấm này, chọn "Manage keys"

4.4.2. Chọn "Create new key" để tạo key JSON cho Service Account. Trong màn hình kế tiếp, chọn loại file là JSON. Nhấn Create.

4.4.3. Sau khi key JSON được tạo, sẽ có 1 file được tải về máy tính của bạn. Đây chính là file key cần thiết để thiết lập việc truy cập BigQuery từ Elton

danger
  • File JSON này chỉ được sinh ra một lần duy nhất, không thể tạo lại, nên bạn hãy lưu trữ nó một cách an toàn
  • Không upload file JSON này lên các dịch vụ đám mây, hay các dịch vụ git như GitHub, GitLab... vì có thể bị đánh cắp để lén truy cập vào hệ thống của bạn
  • Nếu bạn không rành, nên chuyển giao file này cho đội ngũ IT của công ty để họ lưu trữ an toàn

Nội dung của file key JSON này sẽ trông như sau. Hãy lưu ý mục project_id để khi được hỏi ở giao diện Elton thì bạn sẽ điền vào, cũng như sẵn sàng copy nội dung của file JSON để thiết lập "Destination" trong quá trình dùng Elton.

Bạn có thể dùng các ứng dụng như NotePad (Windows), TextEdit (macOS), hoặc Nano (Linux) để xem nội dung file.

Bước 5: Tạo Billing trên Google BigQuery​

  • Tạo billing yêu cầu bạn cung cấp phương thức thanh toán cho project của bạn trên Google BigQuery.
warning

Đây là một bước bắt buộc trong việc setup Google Bigquery. Nếu như bạn không thao tác điền hình thức thanh toán của mình vào thì project trên Google BigQuery của bạn sẽ không được khởi động.

  • Tại thanh menu trên Google BigQuery, chọn Billing, sau đó chọn nút ADD BILLING ACCOUNT.

  • Sau đó hệ thống sẽ đưa bạn đến màn hình chọn đất nước mà công ty bạn đang đăng ký hoạt động. Bạn thao tác chọn đất nước của công ty sở tại.

  • Khi nhấn tiếp tục hệ thống sẽ yêu cầu bạn điền Business name là tên công ty của bạn và điền thông tin thẻ thanh toán hợp lệ. Bạn thao tác điền và nhấn Start free là sẽ hoàn tất phần setup Billing.

Phần 2: Hướng dẫn setup destination và tạo connection trên Elton​

Mục 1: Hướng dẫn tạo destination của Google Bigquery trên Elton​

Bước 1: Truy cập vào Elton và thao tác chọn tạo destination từ Google BigQuery​

  • Sau khi đã tải được file json từ Google BigQuery về máy, bạn truy cập vào Elton, thao tác các bước cơ bản để tạo destination (có thể xem thêm hướng dẫn các bước cơ bản trong phần Thiết lập destination).

  • Tại mục thao tác cài đặt chi tiết các trường thông tin dành cho destination là Google BigQuery như trong hình, các trường thông tin cần điền sẽ có ý nghĩa như hướng dẫn trong bước 2.

Bước 2: Điền các trường thông tin chi tiết​

1. Set up new Destination​

1.1 Destination type​

Hệ thống sẽ mặc định điền loại destination mà bạn đã chọn trước đó vào field này. Trường hợp có nhu cầu đổi loại destination, bạn có thể thao tác điều chỉnh ở đây bằng cách nhấn vào mũi tên trong trường thông tin và chọn các destination được đề xuất bởi Elton.

1.2 Display name​

Đặt tên cho Destination của bạn - tên này sẽ được hiển thị ở danh sách các destination đã được bạn tạo, cũng như các vị trí khác cần định danh khác

tip

Display name là định dạng free text nên bạn có thể đặt bất kỳ tên nào mà bạn muốn. Tuy nhiên, Elton khuyến khích bạn đặt tên dễ hiểu và có yếu tố liên quan đến công ty và loại destination mà bạn đang setup.

Ví dụ về cách đặt tên destination: Elton BigQuery, Elton Google sheet

1.3 Description​

Điền mô tả về destination bạn sắp tạo nếu có

2. Connection​

2.1 JSON Credentials​

Là đoạn code json bạn đã lưu về máy trong Mục 1: Hướng dẫn setup trên Google Bigquery ở trên. Bạn thao tác copy toàn bộ đoạn code đó từ máy và paste vào ô này.

2.2 Project ID​

Là một trường thông tin trong đoạn code json (xem hình bên dưới). Bạn thao tác copy trường thông tin đó và paste vào ô này

warning

Khi thao tác điền project id vào field này, bạn lưu ý chỉ điền giá trị của project id chứ không phải điền nguyên đoạn code.

Ví dụ: Đoạn code là: "project_id": "test" => khi điền vào field bạn vui lòng chỉ điền test.

2.3 Default region​

  • Vùng lãnh thổ mặc định mà server của destination sẽ được chọn để lưu trữ thông tin. Thông thường trường thông tin này Elton sẽ mặc định chọn asia-southeast1.

  • Để tham khảo thêm các vùng lãnh thổ khác, bạn có thể truy cập vào đây.

3. Advanced setting​

info

  • Khi tạo destination là Google BigQuery, Elton sẽ dùng 2 dịch vụ chính của Google Cloud để tạo destination cho bạn: BigQuery Dataset và Google Cloud Storage Bucket(GCS). Định nghĩa cụ thể của hai dịch vụ trên sẽ được giải thích rõ hơn ở các phần bên dưới.

  • Ở phần setup connection có destination là Google Bigquery (xem Mục 3: Hướng dẫn điền các trường thông tin khi setup Connection với Destination là từ Google BigQuery bên dưới), Elton sẽ hỏi bạn chọn hai mode cho connection: Basic Mode hoặc Advanced mode .

  • Trường hợp bạn chọn Basic mode ở phần setup connection - hệ thống sẽ tự động dùng các tường thông tin bạn đã điền trong phần Advanced setting ở phần setup destination này để tạo ra các dataset tương ứng để chứa dữ liệu khi tạo connection trong các bước tiếp theo.

  • Phần Advanced setting ở đây không bắt buộc điền. Vì vậy, nếu bạn bỏ qua bước này, hệ thống sẽ tự động chọn các bucket/dataset có sẵn để chứa các dữ liệu khi thao tác tạo connection. Trường hợp kết nối của bạn chưa có bất kỳ một bucket/dataset nào được tạo, Elton sẽ tạo mới và tự generate tên, cũng như khi tạo connection với lựa chọn là Basic mode sẽ tự động truyền các bảng dữ liệu tới bucket/dataset được tạo tự động đó.

  • Elton khuyến khích bạn nên điền các trường thông tin ở phần Advanced setting này nhằm làm chủ được tên các dataset của bạn khi tạo connection với mode là Basic mode.

Phần ứng dụng của Advanced Setting sẽ được thể hiện tóm gọn trong sơ đồ dưới đây:

3.1 Google Cloud Storage Bucket Name​

  • GCS: Là nơi chứa file data (giống như google drive) trước khi được đưa vào BigQuery. Bạn có thể hình dung Bucket ở bên GCS tương đương 1 folder bên Google Drive.

  • Ở trường thông tin Google Cloud Storage Bucket Name này yêu cầu bạn đặt tên cho Bucket của bạn trên GCS.

  • Google Cloud có quy định rất cụ thể về cách đặt tên cho Bucket, bạn vui lòng tham khảo quy định ở đây hoặc đọc kỹ các mô tả ở dưới.

tip

Tên bucket thông thường sẽ cần phải thoả các điều kiện sau:

  1. Tên bucket chỉ có thể chứa chữ thường, ký tự số, dấu gạch ngang -, dấu gạch dưới _ và dấu chấm ., không dùng spacing.
  2. Tên bucket phải bắt đầu và kết thúc bằng số hoặc chữ cái.
  3. Tên bucket phải chứa 3-63 ký tự. Tên chứa dấu chấm có thể chứa tối đa 222 ký tự, nhưng mỗi thành phần được phân tách bằng dấu chấm không được dài quá 63 ký tự.
warning
  1. Nếu trong tên bucket có dấu chấm ., trong một vài trường hợp Google sẽ yêu cầu bạn xác minh thêm.
  2. Tên bucket không thể được biểu thị dưới dạng địa chỉ IP theo ký hiệu thập phân có dấu chấm (ví dụ: 192.168.5.4)
  3. Tên bucket không thể bắt đầu bằng tiền tố "goog".
  4. Tên bucket không được chứa "google" hoặc đóng lỗi chính tả, chẳng hạn như "g00gle".
  5. Không sử dụng ID người dùng, địa chỉ email, tên dự án, số dự án hoặc bất kỳ thông tin nhận dạng cá nhân (PII) nào trong tên nhóm vì bất kỳ ai cũng có thể thăm dò sự tồn tại của nhóm.

Một vài lưu ý khác khi đặt tên Bucket:

Tên của bucket nằm trong một không gian tên duy nhất được chia sẻ bởi tất cả người dùng Cloud Storage. Điều này có nghĩa rằng:

  1. Mỗi tên nhóm phải là duy nhất trên toàn cầu.
  2. Nếu bạn cố gắng tạo một nhóm có tên đã thuộc về một nhóm hiện có, chẳng hạn như nhóm ví dụ, Cloud Storage sẽ phản hồi bằng thông báo lỗi.
  3. Tên nhóm được hiển thị công khai.
  4. Sau khi bạn xóa một nhóm, bất kỳ ai cũng có thể sử dụng lại tên của nhóm đó cho một nhóm mới.
tip

Một vài ví dụ về đặt tên bucket:

Sau đây là ví dụ về tên nhóm hợp lệ:

my-travel-maps

0f75d593-8e7b-4418-a5ba-cb2970f0b91e

test.example.com (Google sẽ têu cầu xác minh quyền sở hữu đối với example.com trong trường hợp này)

Sau đây là ví dụ về tên nhóm không hợp lệ:

My-Travel-Maps (chứa chữ in hoa)

my_google_bucket (chứa "google")

thùng thử nghiệm (chứa một khoảng trống)

3.2 BigQuery Staging Dataset​

  • BigQuery Dataset là nơi chứa chính thức những bảng dữ liệu được load từ một source nhất định của bạn khi tạo connection. Hình dung Big Query Dataset là một tập tin (folder) chứa các bảng dữ liệu liên quan tới nhau (thường thì chung 1 data source). Mỗi connection khi được tạo sẽ cần dùng 2 dataset:

    a. Một bộ để chứa các bảng tạm nối qua GCS - Chính là BigQuery Staging Dataset

    b. Một bộ để chứa bảng chính sau khi data được load vô - Chính là BigQuery Destination Dataset (đây là bảng chính mà bạn dùng và sẽ được hướng dẫn đặt tên ở mục 3.3).

  • Trường thông tin BigQuery Staging Dataset ở đây yêu cầu bạn đặt tên cho dataset tạm để kết nối qua GCS. quy định đặt tên cho các bộ dataset bạn có thể tham khảo ở đây.

tip

  • Elton khuyến nghị bạn đặt tên theo mẫu sau: [tên_source_bạn_dự_định_kết_nối_đến_destination_này]_[staging]

  • Tất cả chữ cái viết thường và cách nhau bằng dấu underscore _.

  • Ví dụ: facebook_ads_staging

3.3 BigQuery Destination Dataset​

  • Như đã đề cập ở trên, đây là bộ chứa bảng chính sau khi data được load vô và tại đây hệ thống cũng yêu cầu bạn đặt tên cho dataset chính này. Quy định đặt tên cho các bộ dataset chính bạn có thể tham khảo ở đây.
tip

  • Elton khuyến nghị bạn đặt tên cho dataset chính theo mẫu sau: [tên_source_bạn_dự_định_kết_nối_đến_destination_này]_[dwh]

  • Tất cả chữ cái viết thường và cách nhau bằng dấu underscore _.

  • Ví dụ: facebook_ads_dwh

Bước 3: Test connetion và tạo destination​

  • Sau khi điền thông tin xong, bạn tiến hành chọn nút Test connection, hệ thống sẽ tự động test xem destination của bạn đã được cấu hình thành công hay chưa

  • Trường hợp thành công => Bạn thao tác nhấn Save và hoàn thành tạo destination.

  • Trường hợp không thành công => Bạn vui lòng kiểm tra lại các trường thông tin ở trên và tiến hành test lại.

Mục 2: Hướng dẫn điền các trường thông tin khi setup Connection với Destination là từ Google BigQuery​

  • Tại màn hình chi tiết tạo connection có destination là Goolge BigQuery, bạn tiến hành cài đặt các thông tin liên quan đến connection.

  • Đối với loại destination là Google BigQuery, cài đặt destination sẽ có hai mode chính: Basic Mode và Advanced mode

1. Basic Mode​

  • Khi chọn Basic mode , hệ thống sẽ tự động dùng các tường thông tin bạn đã điền trong phần Advanced setting ở phần setup destination trong các bước trước để tạo connection - tạo ra các dataset dùng để chứa dữ liệu dựa trên tên bạn đã setup sẵn.

  • Như đã để cập ở phần 3. Advanced setting, phần Advanced setting trong lúc tạo destination không bắt buộc điền. Vì vậy, nếu bạn bỏ qua, hệ thống sẽ tự động chọn các bucket/dataset có sẵn để chứa các dữ liệu khi thao tác tạo connection. Trường kết nối của bạn chưa có bất kỳ một bucket/dataset nào được tạo, Elton sẽ tạo mới và tự generate tên, cũng như khi tạo connection với lựa chọn là Basic mode sẽ tự động truyền các bảng dữ liệu tới bucket/dataset được tạo tự động đó.

2. Advanced Mode​

  • Khi chọn Advanced mode, bạn có thể tiến hành chọn các Google Cloud Storage Bucket hoặc BigQuery Dataset đã có sẵn, thay vì để Elton tư động chọn cho bạn như khi dùng Basic mode.

  • Advanced mode được khuyến nghị khi các bảng dữ liệu từ source bạn sắp sửa kết nối có chung một loại dữ liệu với những bảng dữ liệu đã có sẵn trong bucket/dataset bạn dự định chọn, mà không cần phải tạo ra một bucket/dataset mới.

  • Tại Advanced mode, bạn cũng có thể chủ động tạo ra các GCS Bucket và BigQuery Dataset. Chi tiết sẽ được hướng dẫn trong các bước bên dưới.

tip

Giữa Advanced mode và Basic mode, Elton khuyến khích bạn dùng Advanced mode vì độ chi tiết và cá nhân hoá tốt hơn cho nhu cầu của bạn.

info

  • Với các bảng dữ liệu có cùng loại, cùng nguồn xuất phát, bạn nên để chúng ở chung 1 staging bucket, và chung 1 dataset trên BigQuery, như vậy sẽ dễ dàng hiểu được nguồn đó có những table nào. Sau này, khi mở rộng data warehouse, việc quản lý sẽ dễ dàng hơn rất nhiều.

  • Ví dụ: Bạn có bảng user, user_info và user_credit từ một database tên company_user, bạn nên tạo advanced mode như sau:

  1. staging bucket: [tên công ty bạn viết tắt]_[company_user_staging]

  2. staging dataset: [tên công ty bạn viết tắt]_[company_user_staging]

  3. dwh dataset: [tên công ty bạn viết tắt]_[company_user_dwh]

  • Một số nguồn dữ liệu sẽ được Elton tự động nhóm các bảng có liên quan vào cùng dataset để giúp bạn sử dụng dễ hơn, ví dụ như Facebook Ads, Facebook Page Insights…

2.1 Google Cloud Storage Config​

2.1.1 Staging GCS Bucket​

  • Bạn thao tác chọn GCS bucket mà bạn dự định sẽ truyền các bảng dữ liệu vào hoặc có thể tạo GCS bucket mới bằng cách điền tên và nhấn Add item từ drop-down list như hình ở dưới.

2.1.2 Staging GCS Prefix​

  • Trường thông tin này cho phép bạn sắp xếp GCS bucket của bạn trên staging bằng cách thêm tiền tố (prefix) vào tên của folder. Ví dụ: bucket (folder) là test, bạn thao tác điền prefix là abcd/xyz thì folder lúc tạo sẽ là abc/xyz/... nằm trong bucket (folder lớn) là test.

  • Đây là một phần tuỳ chỉnh không quá quan trọng và chủ yếu hỗ trợ sự cá nhân hoá trong công ty của bạn, nên bạn có thể cân nhắc bỏ qua không tuỳ chỉnh phần này.

2.2 BigQuery Config​

2.2.1 Staging dataset & Destination dataset​

  • Tương tự như Staging GCS Bucket, bạn thao tác chọn staging dataset mà bạn dự định sẽ truyền các bảng dữ liệu vào hoặc có thể tạo dataset mới bằng cách điền tên và nhấn Add item từ drop-down list.

2.2.2 Auto update schema​

  • Trong một bảng dữ liệu (table) Schema là các yếu tố liên quan đến tên cột, loại dữ liệu, mô tả của cột,..

  • Khi user chọn bật Auto update schema thì mỗi lần dữ liệu sync từ nguồn (source) thay đổi (ví dụ: thay đổi tên cột) thì hệ thống sẽ tự động update dữ liệu của bảng tương ứng ở trên BQ. Khi thay đổi và tự động update, hệ thống cũng sẽ gửi thông báo về group chat của công ty bạn trên Telegram.

  • Trường hợp bạn chọn tắt lựa chọn Auto update schema thì hệ thống sẽ không tự động cập nhật những thay đổi liên quan tới schema từ nguồn lên Google BigQuery. Bạn vẫn sẽ nhận được thông báo trên group chat Telegram như hình bên dưới.

tip

Elton khuyến khích bạn nên bật tự động update schema khi có thay đổi để tránh làm gián đoạn quá trình kết nối dữ liệu

Destination Table Require Partition Filter?​

  • Định nghĩ chung về Partition: Khi một bảng được phân vùng (partitioned), dữ liệu sẽ được sắp xếp theo cột được phân chia để cải thiện hiệu suất và giảm lượng dữ liệu cần quét khi thực hiện truy vấn, thường là trên cột ngày. BigQuery sẽ cho bạn biết bảng có được phân vùng hay không khi bạn nhấp vào bảng đó.

  • Khi chọn bật chế độ này, thì bạn sẽ chỉ cho phép những bảng dữ liệu mà có partition lúc truy xuất (query) phải dùng filter partition thì mới query được.

tip

  • Thông thường những bảng có dung lượng cực lớn mà nhiều người truy cập vô để truy xuất thì mới cần bật lựa chọn này, để hạn chế người dùng dùng lệnh SELECT * full bảng dẫn đến chi phí sử dụng dịch vụ truy xuất dữ liệu BigQuery cao.

  • Với những khách hàng hiện tại của Elton, lượng dữ liệu chưa gọi là quá lớn để vượt quá giới hạn được sử dụng miễn phí của Google BigQuery. Vì vậy, Elton khuyến khích bạn nên tắt lựa chọn này khi setup.

warning
  • Chế độ Destination Table Require Partition Filter? có thể bật lên bất cứ lúc nào trong quá trình sử dụng. Tuy nhiên, một khi bật chế độ Destination Table Require Partition Filter? lên thì sẽ không thay đổi được nữa (bạn phải nhờ Admin Elton điều chỉnh rất phức tạp). Vì vậy bạn cần cân nhắc kỹ trước khi bật chế độ này lên.

Phụ lục 1: Những thuật ngữ cơ bản và cách triển khai một câu lệnh trích xuất dữ liệu cơ bản trên Google BigQuery​

Elton cung cấp thêm cho bạn tài liệu hướng dẫn vỡ lòng cách trích xuất dữ liệu cơ bản trên BigQuery, cùng với định nghĩa về các thuật ngữ cơ bản khi sử dụng BQ cũng như ngông ngữ SQL. Chi tiết xem tại đây (sẽ được update sớm).

Last updated on