連線至 Cloud SQL

BigQuery 管理員可以建立連線，存取 Cloud SQL 資料。資料分析師可透過這項連線查詢 Cloud SQL 中的資料。如要連線至 Cloud SQL，請按照下列步驟操作：

1. 建立 Cloud SQL 連線
2. 授予 BigQuery Connection 服務代理人存取權。

事前準備

1. 選取含有 Cloud SQL 資料庫的專案。
   

   前往專案選取器

2. 啟用 BigQuery Connection API。
   

   啟用 API

3. 確認 Cloud SQL 執行個體具有公開 IP 連線或私人連線：
   

   • 如要保護 Cloud SQL 執行個體，您可以新增公開 IP 連線，但不必提供授權位址。這樣一來，執行個體就無法從公用網際網路存取，但可供 BigQuery 查詢。

   • 如要讓 BigQuery 透過私人連線存取 Cloud SQL 資料，請為新或現有的 Cloud SQL 執行個體設定私人 IP 連線，然後選取「啟用私人路徑」核取方塊。這項服務會使用內部直接路徑，而非虛擬私有雲內的私人 IP 位址。

4. 如要取得建立 Cloud SQL 連線所需的權限，請要求管理員授予您專案的 BigQuery Connection 管理員 (roles/bigquery.connectionAdmin) IAM 角色。如要進一步瞭解如何授予角色，請參閱「管理專案、資料夾和組織的存取權」。

   您或許也能透過自訂角色或其他預先定義的角色，取得必要權限。

建立 Cloud SQL 連線

最佳做法是使用連線處理資料庫憑證，以便連線至 Cloud SQL。連線經過加密，並會安全地儲存在 BigQuery 連線服務中。如果使用者憑證適用於來源中的其他資料，您可以重複使用該連結。舉例來說，您或許可以使用一個連線，查詢位於相同 Cloud SQL 執行個體中的多個資料庫。

選取下列其中一個選項，建立 Cloud SQL 連線：

控制台

1. 前往「BigQuery」頁面

   前往「BigQuery」

2. 在「Explorer」窗格中，按一下 「Add」。

   「新增資料」對話方塊隨即開啟。

3. 在「Filter By」(依條件篩選) 窗格的「Data Source Type」(資料來源類型) 區段中，選取「Databases」(資料庫)。

   或者，您也可以在「Search for data sources」(搜尋資料來源) 欄位中輸入 mysql。

4. 在「精選資料來源」部分，按一下「MySQL」。

5. 按一下「CloudSQL (MySQL)：BigQuery Federation」解決方案資訊卡。

6. 在「外部資料來源」對話方塊中，輸入下列資訊：

   • 在「連線類型」中，選取來源類型，例如「MySQL」或「PostgreSQL」。
   • 在「Connection ID」(連線 ID) 專區中輸入連線資源的 ID。您可以使用英文字母、數字和底線。例如：bq_sql_connection。
   • 在「資料位置」中，選取與外部資料來源區域相容的 BigQuery 位置 (或區域)。
   • 選用：在「Friendly name」(好記名稱) 中輸入使用者容易記得的連線名稱，例如 My connection resource。好記名稱可以是任何資料值，只要您日後需要修改時可以輕鬆識別連線資源即可。
   • 選用：在「Description」(說明) 中輸入這項連線資源的說明。
   • 選用：加密。如要使用客戶自行管理的加密金鑰 (CMEK) 加密憑證，請選取「客戶自行管理的加密金鑰 (CMEK)」，然後選取客戶自行管理的金鑰。否則，系統會以預設 Google-owned and Google-managed encryption key保護您的憑證。
   • 如果連線類型選擇 Cloud SQL MySQL 或 Postgres，請在「Cloud SQL connection name」(Cloud SQL 連線名稱) 中，輸入完整的 Cloud SQL 執行個體名稱，格式通常是 project-id:location-id:instance-id。在要查詢的 Cloud SQL 執行個體詳細資料頁面中，您可以找到執行個體 ID。
   • 在「Database name」(資料庫名稱) 中輸入資料庫名稱。
   • 在「Database username」(資料庫使用者名稱) 中輸入資料庫的使用者名稱。

   • 在「Database password」(資料庫密碼) 中輸入資料庫的密碼。

     • 選用：如要查看密碼，請按一下「顯示密碼」。
     注意： 如果同一組使用者憑證適用於外部資料來源中的其他資料庫，該使用者就能透過相同的連線資源查詢這些資料庫。

7. 點選「建立連線」。

8. 點選「前往連線」。

9. 在「連線資訊」窗格中，複製服務帳戶 ID，以供後續步驟使用。

bq

輸入 bq mk 指令並提供連線旗標 --connection。還需加上以下旗標：

• --connection_type
• --properties
• --connection_credential
• --project_id
• --location

以下旗標為選用項目：

• --display_name：連線的易記名稱。
• --description：連線說明。
• --kms_key_name：客戶自行管理的加密金鑰。如果省略此屬性，系統會以預設的 Google-owned and Google-managed encryption key保護憑證。

connection_id 是選用參數，可做為指令的最後一個引數新增，用於內部儲存。如果未提供連線 ID，系統會自動產生專屬 ID。 connection_id 可以包含字母、數字和底線。

    bq mk --connection --display_name='friendly name' --connection_type=TYPE \
      --properties=PROPERTIES --connection_credential=CREDENTIALS \
      --project_id=PROJECT_ID --location=LOCATION \
      CONNECTION_ID

更改下列內容：

• TYPE：外部資料來源的類型。
• PROPERTIES：已建立連線的 JSON 格式參數。例如：--properties='{"param":"param_value"}'。如要建立連線資源，您必須提供 instanceID、database 和 type 參數。
• CREDENTIALS：參數 username 和 password。
• PROJECT_ID：您的專案 ID。
• LOCATION：Cloud SQL 執行個體所在地區，或對應的多重區域。
• CONNECTION_ID：連線 ID。

舉例來說，下列指令會在 ID 為 federation-test 的專案中新建名為 my_new_connection (好記名稱：「My new connection」) 的連線資源。

bq mk --connection --display_name='friendly name' --connection_type='CLOUD_SQL' \
  --properties='{"instanceId":"federation-test:us-central1:mytestsql","database":"mydatabase","type":"MYSQL"}' \
  --connection_credential='{"username":"myusername", "password":"mypassword"}' \
  --project_id=federation-test --location=us my_connection_id

API

在 BigQuery Connection API 中，您可以在 ConnectionService 內叫用 CreateConnection，以例項化連線。詳情請參閱用戶端程式庫頁面。

Java

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Java 設定操作說明進行操作。詳情請參閱 BigQuery Java API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

import com.google.cloud.bigquery.connection.v1.CloudSqlCredential;
import com.google.cloud.bigquery.connection.v1.CloudSqlProperties;
import com.google.cloud.bigquery.connection.v1.Connection;
import com.google.cloud.bigquery.connection.v1.CreateConnectionRequest;
import com.google.cloud.bigquery.connection.v1.LocationName;
import com.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;
import java.io.IOException;

// Sample to create a connection with cloud MySql database
public class CreateConnection {

  public static void main(String[] args) throws IOException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String location = "MY_LOCATION";
    String connectionId = "MY_CONNECTION_ID";
    String database = "MY_DATABASE";
    String instance = "MY_INSTANCE";
    String instanceLocation = "MY_INSTANCE_LOCATION";
    String username = "MY_USERNAME";
    String password = "MY_PASSWORD";
    String instanceId = String.format("%s:%s:%s", projectId, instanceLocation, instance);
    CloudSqlCredential cloudSqlCredential =
        CloudSqlCredential.newBuilder().setUsername(username).setPassword(password).build();
    CloudSqlProperties cloudSqlProperties =
        CloudSqlProperties.newBuilder()
            .setType(CloudSqlProperties.DatabaseType.MYSQL)
            .setDatabase(database)
            .setInstanceId(instanceId)
            .setCredential(cloudSqlCredential)
            .build();
    Connection connection = Connection.newBuilder().setCloudSql(cloudSqlProperties).build();
    createConnection(projectId, location, connectionId, connection);
  }

  static void createConnection(
      String projectId, String location, String connectionId, Connection connection)
      throws IOException {
    try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
      LocationName parent = LocationName.of(projectId, location);
      CreateConnectionRequest request =
          CreateConnectionRequest.newBuilder()
              .setParent(parent.toString())
              .setConnection(connection)
              .setConnectionId(connectionId)
              .build();
      Connection response = client.createConnection(request);
      System.out.println("Connection created successfully :" + response.getName());
    }
  }
}

授予服務代理存取權

在專案中首次建立 Cloud SQL 連線時，系統會自動建立服務代理程式。服務代理人的名稱為「BigQuery Connection Service Agent」。如要取得服務代理 ID，請查看連線詳細資料。服務代理 ID 的格式如下：

[e-mail]。

如要連線至 Cloud SQL，您必須授予新連線 Cloud SQL 唯讀存取權，這樣 BigQuery 才能代替使用者存取檔案。服務代理必須具備下列權限：

• cloudsql.instances.connect
• cloudsql.instances.get

您可以為與連線相關聯的服務代理人授予 Cloud SQL 用戶端 IAM 角色 (roles/cloudsql.client)，該角色已指派這些權限。如果服務代理已具備必要權限，則可略過下列步驟。

控制台

1. 前往「IAM & Admin」(IAM 與管理) 頁面。

   前往「IAM & Admin」(IAM 與管理)

2. 按一下「 授予存取權」。

   「新增主體」對話方塊隨即開啟。

3. 在「新增主體」欄位中，輸入服務代理程式名稱「BigQuery Connection Service Agent」，或從連線資訊取得服務代理程式 ID。

4. 在「Select a role」(選取角色) 欄位中，依序選取「Cloud SQL」和「Cloud SQL Client」(Cloud SQL 用戶端)。

5. 按一下 [儲存]。

gcloud

使用 gcloud projects add-iam-policy-binding 指令：

gcloud projects add-iam-policy-binding PROJECT_ID --member=serviceAccount:SERVICE_AGENT_ID --role=roles/cloudsql.client

提供以下這些值：

• PROJECT_ID：您的 Google Cloud 專案 ID
• SERVICE_AGENT_ID：從連線資訊取得的服務代理 ID。

注意： 如要進一步瞭解如何授予及撤銷 IAM 角色，請參閱「管理專案、資料夾和機構的存取權」。

與使用者共用連線

您可以授予下列角色，讓使用者查詢資料及管理連線：

• roles/bigquery.connectionUser：可讓使用者透過連線功能連結外部資料來源，並對其執行查詢。

• roles/bigquery.connectionAdmin：讓使用者管理連線。

如要進一步瞭解 BigQuery 中的 IAM 角色和權限，請參閱預先定義的角色和權限一文。

選取下列選項之一：

控制台

1. 前往「BigQuery」頁面

   前往「BigQuery」

   連線會列在專案中，位於「連線」群組。

2. 點選左側窗格中的 「Explorer」：

   

   如果沒有看到左側窗格，請按一下「展開左側窗格」圖示 開啟窗格。

3. 按一下專案，然後依序點選「連線」和所需連線。

4. 在「詳細資料」窗格中，按一下「共用」即可共用連線。 接著，按照下列步驟操作：

   1. 在「連線權限」對話方塊中，新增或編輯主體，與其他主體共用連線。

   2. 按一下 [儲存]。

bq

您無法使用 bq 指令列工具共用連線。 如要共用連線，請使用 Google Cloud 控制台或 BigQuery Connections API 方法共用連線。

API

請使用 BigQuery Connections REST API 參考資料中的 projects.locations.connections.setIAM 方法，並提供 policy 資源的執行個體。

Java

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Java 設定操作說明進行操作。詳情請參閱 BigQuery Java API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

import com.google.api.resourcenames.ResourceName;
import com.google.cloud.bigquery.connection.v1.ConnectionName;
import com.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;
import com.google.iam.v1.Binding;
import com.google.iam.v1.Policy;
import com.google.iam.v1.SetIamPolicyRequest;
import java.io.IOException;

// Sample to share connections
public class ShareConnection {

  public static void main(String[] args) throws IOException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String location = "MY_LOCATION";
    String connectionId = "MY_CONNECTION_ID";
    shareConnection(projectId, location, connectionId);
  }

  static void shareConnection(String projectId, String location, String connectionId)
      throws IOException {
    try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
      ResourceName resource = ConnectionName.of(projectId, location, connectionId);
      Binding binding =
          Binding.newBuilder()
              .addMembers("group:[e-mail]")
              .setRole("roles/bigquery.connectionUser")
              .build();
      Policy policy = Policy.newBuilder().addBindings(binding).build();
      SetIamPolicyRequest request =
          SetIamPolicyRequest.newBuilder()
              .setResource(resource.toString())
              .setPolicy(policy)
              .build();
      client.setIamPolicy(request);
      System.out.println("Connection shared successfully");
    }
  }
}

後續步驟

• 瞭解不同連線類型。
• 瞭解如何管理連線。
• 瞭解聯合查詢。
• 瞭解如何查詢 Cloud SQL 資料。