搭配 Dataproc 使用 Trino

Trino (舊稱 Presto) 是分散式 SQL 查詢引擎，可查詢分散於一或多個異質資料來源的大型資料集。Trino 可透過連接器查詢 Hive、MySQL、Kafka 和其他資料來源。本教學課程將示範如何：

  • 在 Dataproc 叢集上安裝 Trino 服務
  • 從本機電腦上安裝的 Trino 用戶端查詢公開資料，該用戶端會與叢集中的 Trino 服務通訊
  • 透過 Trino Java JDBC 驅動程式，從與叢集中的 Trino 服務通訊的 Java 應用程式執行查詢。

目標

  • 建立已安裝 Trino 的 Dataproc 叢集
  • 準備資料。本教學課程使用 芝加哥計程車車程公開資料集，可在 BigQuery 中取得。
    1. 從 BigQuery 擷取資料
    2. 將資料以 CSV 檔案的形式載入至 Cloud Storage
    3. 轉換資料：
      1. 將資料公開為 Hive 外部資料表，讓 Trino 可查詢資料
      2. 將資料從 CSV 格式轉換為 Parquet 格式，加快查詢速度
  • 分別使用 SSH 通道或 Trino JDBC 驅動程式，將 Trino CLI 或應用程式程式碼查詢傳送至叢集中執行的 Trino 協調器
  • 透過 Trino Web UI 檢查記錄並監控 Trino 服務

    • 費用

    在本文件中，您會使用 Google Cloud的下列計費元件：

    您可以使用 Pricing Calculator 根據預測用量產生預估費用。 新 Google Cloud 使用者可能符合申請免費試用的資格。

    事前準備

    如果您尚未建立專案和 Cloud Storage 值區，請先建立這兩項項目，以便保存本教學課程中使用的資料。 Google Cloud 1. 設定專案
    1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

    2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

      Go to project selector

    3. Make sure that billing is enabled for your Google Cloud project.

    4. Enable the Dataproc, Compute Engine, Cloud Storage, and BigQuery APIs.

      Enable the APIs

    5. Install the Google Cloud CLI.

    6. If you're using an external identity provider (IdP), you must first sign in to the gcloud CLI with your federated identity.

    7. To initialize the gcloud CLI, run the following command: 

      gcloud init

    8. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

      Go to project selector

    9. Make sure that billing is enabled for your Google Cloud project.

    10. Enable the Dataproc, Compute Engine, Cloud Storage, and BigQuery APIs.

      Enable the APIs

    11. Install the Google Cloud CLI.

    12. If you're using an external identity provider (IdP), you must first sign in to the gcloud CLI with your federated identity.

    13. To initialize the gcloud CLI, run the following command: 

      gcloud init
      14. 1. 在專案中建立 Cloud Storage 值區，用於保存本教學課程中使用的資料。
      1. In the Google Cloud console, go to the Cloud Storage Buckets page.

        Go to Buckets

      2. Click Create.
      3. On the Create a bucket page, enter your bucket information. To go to the next step, click Continue.
        1. In the Get started section, do the following:
          • Enter a globally unique name that meets the bucket naming requirements.
          • To add a bucket label, expand the Labels section (), click Add label, and specify a key and a value for your label.
        2. In the Choose where to store your data section, do the following:
          1. Select a Location type.
          2. Choose a location where your bucket's data is permanently stored from the Location type drop-down menu.
          3. To set up cross-bucket replication, select Add cross-bucket replication via Storage Transfer Service and follow these steps:

            Set up cross-bucket replication

            1. In the Bucket menu, select a bucket.

            2. In the Replication settings section, click Configure to configure settings for the replication job.

              The Configure cross-bucket replication pane appears.

              • To filter objects to replicate by object name prefix, enter a prefix that you want to include or exclude objects from, then click Add a prefix.
              • To set a storage class for the replicated objects, select a storage class from the Storage class menu. If you skip this step, the replicated objects will use the destination bucket's storage class by default.
              • Click Done.
        3. In the Choose how to store your data section, do the following:
          1. Select a default storage class for the bucket or Autoclass for automatic storage class management of your bucket's data.
          2. To enable hierarchical namespace, in the Optimize storage for data-intensive workloads section, select Enable hierarchical namespace on this bucket.
        4. In the Choose how to control access to objects section, select whether or not your bucket enforces public access prevention, and select an access control method for your bucket's objects.
        5. In the Choose how to protect object data section, do the following:
          • Select any of the options under Data protection that you want to set for your bucket.
            • To enable soft delete, click the Soft delete policy (For data recovery) checkbox, and specify the number of days you want to retain objects after deletion.
            • To set Object Versioning, click the Object versioning (For version control) checkbox, and specify the maximum number of versions per object and the number of days after which the noncurrent versions expire.
            • To enable the retention policy on objects and buckets, click the Retention (For compliance) checkbox, and then do the following:
              • To enable Object Retention Lock, click the Enable object retention checkbox.
              • To enable Bucket Lock, click the Set bucket retention policy checkbox, and choose a unit of time and a length of time for your retention period.
          • To choose how your object data will be encrypted, expand the Data encryption section (), and select a Data encryption method.
      4. Click Create.

    建立 Dataproc 叢集

    使用 optional-components 標記 (適用於 2.1 以上版本的映像檔) 建立 Dataproc 叢集，即可在叢集中安裝 Trino 選用元件，並使用 enable-component-gateway 標記啟用 元件閘道，方便您透過 Google Cloud 控制台存取 Trino Web UI。

    1. 設定環境變數：
      • PROJECT：您的專案 ID
      • BUCKET_NAME:您在「事前準備」中建立的 Cloud Storage 值區名稱
      • REGION： region (本教學課程所用叢集的建立位置，例如「us-west1」)
      • WORKERS：本教學課程建議使用 3 至 5 個 worker
      export PROJECT=project-id
export WORKERS=number
export REGION=region
export BUCKET_NAME=bucket-name
    2. 在本機電腦上執行 Google Cloud CLI，建立叢集。
      gcloud beta dataproc clusters create trino-cluster \
    --project=${PROJECT} \
    --region=${REGION} \
    --num-workers=${WORKERS} \
    --scopes=cloud-platform \
    --optional-components=TRINO \
    --image-version=2.1  \
    --enable-component-gateway

    準備資料

    將 bigquery-public-data chicago_taxi_trips 資料集匯出至 Cloud Storage 做為 CSV 檔案，然後建立 Hive 外部資料表來參照資料。

    1. 在本機上執行下列指令，將計程車資料從 BigQuery 匯入為 CSV 檔案 (不含標題)，並匯入您在開始前建立的 Cloud Storage 值區。
      bq --location=us extract --destination_format=CSV \
     --field_delimiter=',' --print_header=false \
       "bigquery-public-data:chicago_taxi_trips.taxi_trips" \
       gs://${BUCKET_NAME}/chicago_taxi_trips/csv/shard-*.csv
    2. 建立 Hive 外部資料表，並由 Cloud Storage 值區中的 CSV 和 Parquet 檔案提供支援。
      1. 建立 Hive 外部資料表 chicago_taxi_trips_csv。
        gcloud dataproc jobs submit hive \
    --cluster trino-cluster \
    --region=${REGION} \
    --execute "
        CREATE EXTERNAL TABLE chicago_taxi_trips_csv(
          unique_key   STRING,
          taxi_id  STRING,
          trip_start_timestamp  TIMESTAMP,
          trip_end_timestamp  TIMESTAMP,
          trip_seconds  INT,
          trip_miles   FLOAT,
          pickup_census_tract  INT,
          dropoff_census_tract  INT,
          pickup_community_area  INT,
          dropoff_community_area  INT,
          fare  FLOAT,
          tips  FLOAT,
          tolls  FLOAT,
          extras  FLOAT,
          trip_total  FLOAT,
          payment_type  STRING,
          company  STRING,
          pickup_latitude  FLOAT,
          pickup_longitude  FLOAT,
          pickup_location  STRING,
          dropoff_latitude  FLOAT,
          dropoff_longitude  FLOAT,
          dropoff_location  STRING)
        ROW FORMAT DELIMITED
        FIELDS TERMINATED BY ','
        STORED AS TEXTFILE
        location 'gs://${BUCKET_NAME}/chicago_taxi_trips/csv/';"
      2. 確認 Hive 外部資料表已建立。
        gcloud dataproc jobs submit hive \
    --cluster trino-cluster \
    --region=${REGION} \
    --execute "SELECT COUNT(*) FROM chicago_taxi_trips_csv;"
      3. 建立另一個具有相同欄位的 Hive 外部資料表 chicago_taxi_trips_parquet，但資料儲存格式為 Parquet，以便提升查詢效能。
        gcloud dataproc jobs submit hive \
    --cluster trino-cluster \
    --region=${REGION} \
    --execute "
        CREATE EXTERNAL TABLE chicago_taxi_trips_parquet(
          unique_key   STRING,
          taxi_id  STRING,
          trip_start_timestamp  TIMESTAMP,
          trip_end_timestamp  TIMESTAMP,
          trip_seconds  INT,
          trip_miles   FLOAT,
          pickup_census_tract  INT,
          dropoff_census_tract  INT,
          pickup_community_area  INT,
          dropoff_community_area  INT,
          fare  FLOAT,
          tips  FLOAT,
          tolls  FLOAT,
          extras  FLOAT,
          trip_total  FLOAT,
          payment_type  STRING,
          company  STRING,
          pickup_latitude  FLOAT,
          pickup_longitude  FLOAT,
          pickup_location  STRING,
          dropoff_latitude  FLOAT,
          dropoff_longitude  FLOAT,
          dropoff_location  STRING)
        STORED AS PARQUET
        location 'gs://${BUCKET_NAME}/chicago_taxi_trips/parquet/';"
      4. 將 Hive CSV 資料表中的資料載入 Hive Parquet 資料表。
        gcloud dataproc jobs submit hive \
    --cluster trino-cluster \
    --region=${REGION} \
    --execute "
        INSERT OVERWRITE TABLE chicago_taxi_trips_parquet
        SELECT * FROM chicago_taxi_trips_csv;"
      5. 確認資料已正確載入。
        gcloud dataproc jobs submit hive \
    --cluster trino-cluster \
    --region=${REGION} \
    --execute "SELECT COUNT(*) FROM chicago_taxi_trips_parquet;"

    執行查詢

    您可以在本機上透過 Trino CLI 或應用程式執行查詢。

    Trino CLI 查詢

    本節將示範如何使用 Trino CLI 查詢 Hive Parquet 計程車資料集。

    1. 在本機電腦上執行下列指令，使用 SSH 連結至叢集的主要節點。本機終端機會在執行指令期間停止回應。
      gcloud compute ssh trino-cluster-m
    2. 在叢集主要節點的 SSH 終端機視窗中，執行 Trino CLI，連線至主要節點上執行的 Trino 伺服器。
      trino --catalog hive --schema default
    3. 在 trino:default 提示中，確認 Trino 可以找到 Hive 資料表。
      show tables;

      Table
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
 chicago_taxi_trips_csv
 chicago_taxi_trips_parquet
(2 rows)
    4. 透過 trino:default 提示執行查詢，並比較查詢 Parquet 與 CSV 資料的效能。
      • Parquet 資料查詢
        select count(*) from chicago_taxi_trips_parquet where trip_miles > 50;

         _col0
‐‐‐‐‐‐‐‐
 117957
(1 row)
        
Query 20180928_171735_00006_2sz8c, FINISHED, 3 nodes
Splits: 308 total, 308 done (100.00%)
0:16 [113M rows, 297MB] [6.91M rows/s, 18.2MB/s]
      • CSV 資料查詢
        select count(*) from chicago_taxi_trips_csv where trip_miles > 50;

        _col0
‐‐‐‐‐‐‐‐
 117957
(1 row)
        
Query 20180928_171936_00009_2sz8c, FINISHED, 3 nodes
Splits: 881 total, 881 done (100.00%)
0:47 [113M rows, 41.5GB] [2.42M rows/s, 911MB/s]

    Java 應用程式查詢

    如要透過 Trino Java JDBC 驅動程式，從 Java 應用程式執行查詢，請按照下列步驟操作：下載 Trino Java JDBC 驅動程式。1. 在 Maven pom.xml 中新增 trino-jdbc 依附元件。

    <dependency>
  <groupId>io.trino</groupId>
  <artifactId>trino-jdbc</artifactId>
  <version>376</version>
</dependency>
    Java 程式碼範例 
    package dataproc.codelab.trino;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class TrinoQuery {
  private static final String URL = "jdbc:trino://trino-cluster-m:8080/hive/default";
  private static final String SOCKS_PROXY = "localhost:1080";
  private static final String USER = "user";
  private static final String QUERY =
      "select count(*) as count from chicago_taxi_trips_parquet where trip_miles > 50";
  public static void main(String[] args) {
    try {
      Properties properties = new Properties();
      properties.setProperty("user", USER);
      properties.setProperty("socksProxy", SOCKS_PROXY);
      Connection connection = DriverManager.getConnection(URL, properties);
      try (Statement stmt = connection.createStatement()) {
        ResultSet rs = stmt.executeQuery(QUERY);
        while (rs.next()) {
          int count = rs.getInt("count");
          System.out.println("The number of long trips: " + count);
        }
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

    記錄和監控

    記錄

    Trino 記錄位於叢集主要節點和工作站節點的 /var/log/trino/ 中。

    網路使用者介面

    如要在本機瀏覽器中開啟在叢集主要節點上執行的 Trino Web UI，請參閱「查看及存取元件閘道網址」一文。

    監控

    Trino 會透過執行階段資料表公開叢集執行階段資訊。在 Trino 工作階段 (來自 trino:default) 提示中，執行下列查詢，查看執行階段資料表資料：

    select * FROM system.runtime.nodes;

    清除所用資源

    完成教學課程後，您可以清除所建立的資源，這樣資源就不會占用配額並產生費用。下列各節將說明如何刪除或關閉這些資源。

    刪除專案

    如要避免付費，最簡單的方法就是刪除您為了本教學課程所建立的專案。

    如要刪除專案：

    1. In the Google Cloud console, go to the Manage resources page.

      Go to Manage resources

    2. In the project list, select the project that you want to delete, and then click Delete.
    3. In the dialog, type the project ID, and then click Shut down to delete the project.

    刪除叢集

    • 如要刪除叢集：
      gcloud dataproc clusters delete --project=${PROJECT} trino-cluster \
    --region=${REGION}

    刪除值區

    • 如要刪除您在事前準備中建立的 Cloud Storage 值區，包括儲存在值區中的資料檔案：
      gcloud storage rm gs://${BUCKET_NAME} --recursive