搭配 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 的使用者可能符合免費試用資格。

    事前準備

    請建立 Google Cloud 專案和 Cloud Storage bucket，用來存放本教學課程使用的資料。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 bucket，以保存本教學課程中使用的資料。
      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 網頁版 UI。

    1. 設定環境變數：
      • PROJECT：您的專案 ID
      • BUCKET_NAME:您在「事前準備」中建立的 Cloud Storage 值區名稱
      • REGION： region 本教學課程中使用的叢集將建立於此，例如「us-west1」
      • 工作站：建議使用 3 到 5 個工作站進行本教學課程
      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 bucket。 
      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 bucket 中的 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 應用程式執行查詢，請按照下列步驟操作： 1. 下載 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 網頁 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 bucket，包括儲存在 bucket 中的資料檔案，請按照下列步驟操作：
      gcloud storage rm gs://${BUCKET_NAME} --recursive