Đang tải Terabyte dữ liệu từ Postgres vào BigQuery


Hồ Thy Khanh
10 tháng trước
Hữu ích 2 Chia sẻ Viết bình luận 0
Đã xem 8744

Mặc dù thực tế rằng một nhiệm vụ ETL khá khó khăn khi tải các tập dữ liệu lớn, vẫn có kịch bản bạn có thể tải terabyte dữ liệu từ Postgres vào BigQuery tương đối dễ dàng và rất hiệu quả. Đây là trường hợp khi bạn có rất nhiều dữ liệu bất biến được phân phối trong các bảng theo dấu thời gian. Ví dụ: một transactionsbảng có created_atcột dấu thời gian. BigQuery và Postgres có các công cụ tuyệt vời để thực hiện việc này khá nhanh chóng và thuận tiện.

Chuẩn bị bàn Postgres

Định dạng lưu trữ Tụ của BigQuery  , giống như nhiều định dạng dữ liệu lớn khác, được tối ưu hóa cho một lần ghi toàn bộ bảng. Để phân phối dữ liệu giữa các bảng, BigQuery chủ yếu dựa vào mẫu bảng thẻ hoang dã . Cách sử dụng thông thường là tạo các bảng có tên được thêm vào bởi một số giá trị trường. Thông thường, hậu tố ngày được sử dụng cho mục đích này. Ví dụ: bảng transactions20180301có thể chứa các giao dịch được tạo vào ngày 1 tháng 3 năm 2018. Cách tiếp cận này cho phép cả việc tạo lưu trữ rất hiệu quả ở phía BigQuery và để dễ dàng tải nguồn dữ liệu ở phía lưu trữ.

Để thực hiện phương pháp tải này, chúng tôi cần đảm bảo có các chỉ mục phù hợp trong cơ sở dữ liệu Postgres trước khi chúng tôi bắt đầu tải các quy trình từ Postgres vào BigQuery. Để tạo điều này, bạn có thể sử dụng PSQL để kết nối với cơ sở dữ liệu của bạn. Hãy tạo một chỉ mục cho created_atdấu thời gian trong transactionsbảng. Giả sử chúng ta đang làm việc với một máy Ubuntu lưu trữ cá thểDBb, nó có thể đơn giản như:

$ sudo su - postgres
$ psql yourdbname

yourdbname=> create index transactions_created_at on transactions (created_at);

Tạo chỉ mục có thể mất một lúc nếu bạn có nhiều dữ liệu. Bạn có thể theo dõi quá trình thực hiện bằng cách sử dụng toptiện ích. Bạn nên lặp lại việc tạo chỉ mục cho mỗi bảng bạn sẽ tải vào BigQuery.

Đang tải dữ liệu vào BigQuery

Để tải dữ liệu vào BigQuery, chúng tôi sẽ sử dụng BigQuery CLI, đây là một công cụ rất linh hoạt. Bạn có thể cài đặt nó bằng cách sử dụng các hướng dẫn này . Như chúng tôi trên Linux, chúng tôi sẽ sử dụng tập lệnh bash để thực hiện tất cả công việc. Tôi giả sử BigQuery CLI đã được cài đặt và ủy quyền.

Hãy tạo bigquery-upload.shvà thêm chức năng sau để tải lên một ngày từ một bảng cụ thể:

#!/bin/bash

function upload_day {
  table=$1
  sel=$2
  day=$3
  next_day=$(date -d "$day+1 days" +%Y-%m-%d)
  bq_suffix=$(date -d "$day" +%Y%m%d)
  echo "Uploading $table: $day..."
  psql <yourdbname> -c "\\copy (select $sel from $table where created_at >= '$day' and created_at < '$next_day') TO '$table-$day.csv' WITH CSV HEADER"
  gzip $table-$day.csv
  bq load --allow_quoted_newlines --project_id <your-project-id> --replace --source_format=CSV --autodetect --max_bad_records 100 <yourdbname>.$table$bq_suffix $table-$day.csv.gz
  rm $table-$day.csv.gz
};

Hàm này có ba đối số: bảng, cột để chọn và ngày để tải lên. Như bạn có thể thấy, nó sử dụng \copythao tác để tải xuống CSV từ Postgres và sau đó nén nó. Các tài liệu BigQuery nói rằng việc tải CSV nén chậm hơn không nén, nhưng tải lên dữ liệu không nén hầu như luôn có vẻ chậm hơn.

Bạn có thể gọi hàm này bằng cách thêm một dòng ở cuối tập lệnh:

upload_day 'transactions' '*' '2018-03-01'

Điều này sẽ tạo transactions20180301bảng với một ngày dữ liệu. Để tự động hóa quá trình tải lên, chúng tôi có thể giới thiệu một chức năng khác:

function upload_table {
  t=$1
  s=$2
  start_date=$3
  end_date=$4
  while [ "$start_date" != "$end_date" ]; do
       upload_day "$t" "$s" "$start_date"
       start_date=$(date -d "$start_date+1 days" +%Y-%m-%d)
  done
}

Đây upload_tablechức năng có 4 đối số: tên bảng, các cột cho lựa chọn, ngày bắt đầu và ngày kết thúc.

Bạn có thể làm cho tập lệnh này được tham số hóa bằng cách thêm upload_table "$1" '*' "$2" "$3"vào dòng cuối cùng để toàn bộ tập lệnh sẽ là:

#!/bin/bash

function upload_day {
  table=$1
  sel=$2
  day=$3
  next_day=$(date -d "$day+1 days" +%Y-%m-%d)
  bq_suffix=$(date -d "$day" +%Y%m%d)
  echo "Uploading $table: $day..."
  psql <yourdbname> -c "\\copy (select $sel from $table where created_at >= '$day' and created_at < '$next_day') TO '$table-$day.csv' WITH CSV HEADER"
  gzip $table-$day.csv
  bq load --allow_quoted_newlines --project_id <your-project-id> --replace --source_format=CSV --autodetect --max_bad_records 100 <yourdbname>.$table$bq_suffix $table-$day.csv.gz
  rm $table-$day.csv.gz
};

function upload_table {
  t=$1
  s=$2
  start_date=$3
  end_date=$4
  while [ "$start_date" != "$end_date" ]; do
       upload_day "$t" "$s" "$start_date"
       start_date=$(date -d "$start_date+1 days" +%Y-%m-%d)
  done
}

upload_table "$1" '*' "$2" "$3"

Đừng quên $ chmod +x bigquery-upload.shkịch bản của bạn. Sau đó, bạn có thể chạy nó dưới dạng:

$ ./bigquery-upload.sh "transactions" "2017-01-01" "2018-03-01"

Điều tuyệt vời về tập lệnh tải dữ liệu từ Postgres này vào BigQuery là bạn có thể sử dụng nó để tải lên chỉ trong một ngày và nó có thể được đặt trong thư mục /etc/cron.dailyhoặc /etc/cron.hourlyđể bạn có thể lấy dữ liệu của mình trong thời gian gần như thật. Tập --replacelệnh tùy chọn có thể được chạy nhiều lần trong cùng một ngày để thay thế dữ liệu khi nó được làm mới.

Nếu tất cả được thực hiện đúng, nó sẽ tải terabyte dữ liệu của bạn trong một ngày hoặc lâu hơn. Xin đừng ngần ngại để lại ý kiến ​​của bạn!

Hữu ích 2 Chia sẻ Viết bình luận 0
Đã xem 8744