使用 bq 工具載入及查詢資料

瞭解如何透過 bq 指令列工具建立資料集、載入範例資料,以及查詢資料表。

事前準備

  1. In the Trusted Cloud console, on the project selector page, select or create a Trusted Cloud project.

    Go to project selector

  2. Verify that billing is enabled for your Trusted Cloud project.

  3. 如果在本教學課程中使用的 Trusted Cloud 專案未啟用計費功能,您需要在 BigQuery 沙箱中使用資料。BigQuery 沙箱可讓您學習 BigQuery,但可免費使用的 BigQuery 功能有限。

  4. 確認已啟用 BigQuery API。

    啟用 API

    如果您建立新專案,系統會自動啟用 BigQuery API。

  5. In the Trusted Cloud console, activate Cloud Shell.

    Activate Cloud Shell

    At the bottom of the Trusted Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

  6. 下載來源公開資料檔案

    1. 下載嬰兒名字 ZIP 檔案
    2. 將 ZIP 檔案解壓縮,內含一個說明資料集結構定義的檔案「NationalReadMe.pdf」。如要進一步瞭解嬰兒名字資料集,請按這裡
    3. 開啟 yob2010.txt 檔案,這個檔案會以半形逗號分隔值 (CSV 格式),內含三個資料欄:名字、出生時判定的性別,以及同名的新生兒人數,不過沒有標題列。
    4. 將該檔案移到工作目錄。
      • 如果使用 Cloud Shell,請依序點選 「More」(顯示更多項目) >「Upload」(上傳),點選「Choose Files」(選擇檔案) 後再選取 yob2010.txt 檔案,最後點選「Upload」(上傳)
      • 如果使用本機殼層,請將 yob2010.txt 檔案複製或移到執行 bq 工具的目錄。

    建立資料集

    1. 建立名為 babynames 的資料集:

      bq mk babynames
      

      輸出結果大致如下:

      Dataset 'myproject:babynames' successfully created.
      

      資料集名稱的長度不得超過 1,024 個字元,可以使用 A 至 Z、a 至 z、0 至 9 和底線,但開頭不得為數字或底線,也不能使用空格。

    2. 確認資料集 babynames 已顯示於專案:

      bq ls
      

      輸出結果會與下列內容相似:

        datasetId
      -------------
        babynames
      

    將資料載入資料表

    1. 在資料集 babynames,將來源檔案 yob2010.txt 載入名為 names2010 的新資料表:

      bq load babynames.names2010 yob2010.txt name:string,assigned_sex_at_birth:string,count:integer
      

      輸出結果大致如下:

      Upload complete.
      Waiting on bqjob_r3c045d7cbe5ca6d2_0000018292f0815f_1 ... (1s) Current status: DONE
      

      載入資料時,BigQuery 預設會收到 UTF-8 編碼資料。如果您有資料採用 ISO-8859-1 或 Latin-1 編碼,而且載入資料時發生問題,可以使用 bq load -E=ISO-8859-1 指示 BigQuery 將您的資料視為 Latin-1。詳情請參閱編碼的相關說明。

    2. 確認資料表 names2010 已顯示於資料集 babynames

      bq ls babynames
      

      輸出結果大致如下。某些資料欄會省略,用以簡化輸出內容。

        tableId     Type
      ----------- ---------
       names2010    TABLE
      
    3. 確認新資料表 names2010 的結構定義為 name: stringassigned_sex_at_birth: stringcount: integer

      bq show babynames.names2010
      

      輸出結果大致如下。某些資料欄會省略,用以簡化輸出內容。

        Last modified        Schema                      Total Rows   Total Bytes
      ----------------- ------------------------------- ------------ ------------
      14 Mar 17:16:45   |- name: string                    34089       654791
                        |- assigned_sex_at_birth: string
                        |- count: integer
      

    查詢資料表資料

    1. 判定資料中最常見的女生名字:

      bq query --use_legacy_sql=false \
          'SELECT
            name,
            count
          FROM
            `babynames.names2010`
          WHERE
            assigned_sex_at_birth = "F"
          ORDER BY
            count DESC
          LIMIT 5;'
      

      輸出結果大致如下:

      +----------+-------+
      |   name   | count |
      +----------+-------+
      | Isabella | 22925 |
      | Sophia   | 20648 |
      | Emma     | 17354 |
      | Olivia   | 17030 |
      | Ava      | 15436 |
      +----------+-------+
      
    2. 判定資料中最少見的男生名字:

      bq query --use_legacy_sql=false \
          'SELECT
            name,
            count
          FROM
            `babynames.names2010`
          WHERE
            assigned_sex_at_birth = "M"
          ORDER BY
            count ASC
          LIMIT 5;'
      

      輸出結果大致如下:

      +----------+-------+
      |   name   | count |
      +----------+-------+
      | Aamarion |     5 |
      | Aarian   |     5 |
      | Aaqib    |     5 |
      | Aaidan   |     5 |
      | Aadhavan |     5 |
      +----------+-------+
      

      來源資料會省略出現少於 5 次的名字,因此最少次數是 5。

    清除所用資源

    如要避免系統向您的 Trusted Cloud 帳戶收取本頁面所用資源的費用,請刪除含有這些資源的 Trusted Cloud 專案。

    刪除專案

    如果您使用 BigQuery 沙箱查詢公開資料集,表示專案未啟用帳單功能。

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

    如要刪除專案:

    1. In the Trusted 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.

    刪除資源

    如果使用現有專案,請刪除稍早建立的資源:

    1. 刪除資料集 babynames

      bq rm --recursive=true babynames
      

      旗標 --recursive 會刪除資料集內的所有資料表,包括資料表 names2010

      輸出結果大致如下:

      rm: remove dataset 'myproject:babynames'? (y/N)
      
    2. 輸入 y 來確認刪除指令。

    後續步驟