Ciao Space
文章
專案
聯絡我們

BigQuery連接S3內的json檔案作為外部table

2022/11/07

BigQuery Omni

ref

透過BigQuery Omni可直接在BigQuery中存取外部雲端平台的data store

例如AWS S3、Azure Blob Storage

 

好處

若有資料在別的雲端平台

不用透過定時搬移、複製等方式同步至GCP中

而是直接透過BigQuery即時存取跨雲平台的資料

 

設定AWS IAM Policy

依照官方文件設定即可

 

Policy JSON

{
   "Version":"2012-10-17",
   "Statement":[
      {
         "Effect":"Allow",
         "Action":[
            "s3:ListBucket"
         ],
         "Resource":[
            "arn:aws:s3:::bucket-name"
         ]
      },
      {
         "Effect":"Allow",
         "Action":[
            "s3:GetObject"
         ],
         "Resource":[
            "arn:aws:s3:::bucket-name",
            "arn:aws:s3:::bucket-name/*"
         ]
      }
   ]
}

 

建立AWS IAM Role

一樣依照官方文件設定即可

Trusted entity: Web Identity

Provider: Google

Audience: 先設定為00000就好

create_role_step1.png

 

 

建立S3 Connection

ref

 

建立External data source

在Console BigQuery頁面中點擊Explorer旁的"ADD DATA"按鈕

再點擊"Connections to external data"

create_data_source.png

 

設定BigLake on AWS

主要設定如下

  • Connection type: BigLake on AWS
  • Conection ID: 自行設定
  • Connection location: 目前只支援一個region不用特別選
  • AWS role id: 剛才上面設定的AWS IAM Role ARN

create_connect.png

 

設定完成

設定完成之後會看到下方畫面

要將BigQuery Google Identity的值複製起來

後續需要在AWS設定上使用

connection-detail.png

 

為AWS IAM Role設定Trust Relationship

依照官方文件前往IAM Role

將Maximum session duration設定為12小時

設定Trust Relationship(Policy JSON如下)

accounts.google.com:sub要設定為前面開好的BigQuery external connection BigQuery Google Identity

 

{
   "Version":"2012-10-17",
   "Statement":[
      {
         "Effect":"Allow",
         "Principal":{
            "Federated":"accounts.google.com"
         },
         "Action":"sts:AssumeRoleWithWebIdentity",
         "Condition":{
            "StringEquals":{
               "accounts.google.com:sub":"bigquery-google-identity"
            }
         }
      }
   ]
}

 

建立dataset/table

 

建立一個dataset

region一定要選擇"aws-us-east-1"

 

建立table

主要設定如下

  • Create table from: Amazon S3
  • S3 path: s3://BUCKET_NAME/PATH
  • File format: JSONL
  • Connection ID: 選擇前面建立的Connection

create-table.png

 

Schema可使用Auto detect

建立的時候將會自動去抓符合的JSON把裡面的key全部設定為schema field

 

S3 Path wildcard支援

S3 Path可使用wildcard

例如: s3://my-bucket/folder1/*-data.json

BigQuery Omni會自動讀取folder1目錄底下所有檔名包含"-data.json"的檔案(可接受巢狀結構)

 

關於JSONL格式

JSONL(Newline delimited JSON)

透過換行來區分資料的JSON格式

 

一般JSON多筆紀錄範例格式

[
 {"name": "A", "age": 30},
 {"name": "B", "age": 40}
]

 

JSONL格式

{"name": "A", "age": 30}
{"name": "B", "age": 40}

 

支援的Region

ref

BigQuery Omni支援的Region非常少

  • AWS: aws-us-east-1
  • Azure: azure-eastus2

 

限制

ref

這邊提出幾個比較重要的限制

  • 所有External Table的限制都包含
  • 每次查詢結果大小最多10GB
  • 每個project的一天的總查詢大小1TB

 

注意事項

 

JSON格式不可包含Schema不允許的field name規則

因為JSON的key會被拿來當schema field

所以JSON的key如果出現schema不允許的規則

就會出現"Invalid field name"錯誤

 

例如JSON使用一個"foo-bar"欄位

schema不允許dash符號

{"eventId": "event-10", "service": "ec2", "action": "ec2.instance.update", "foo-bar": "foobar" }

 

這時候就會出現這種錯誤

invalid-field-name.png

 

Schema不會自動更新

即使建立table的時候schema使用auto detect

若新匹配的json出現新的欄位

也不會出現在Query結果中

 

例如建立table的時候匹配的JSON格式只有"name"跟"age"

{"name": "A", "age": 30}
{"name": "B", "age": 40}

 

若後續新匹配的JSON出現新的欄位"city"

Query仍可以正常使用

但query中無法使用city欄位

即使用"SELECT *"SQL Query結果也不會顯示city欄位

必須要手動新增schema field才能使用city欄位

{"name": "C", "age": 29, "city": "Taipei"}
{"name": "D", "age": 35, "city": "Taichung"}

 

使用了非JSONL格式

將會出現"Fail to parse JSON"的錯誤

normal-json-fail.png

 

不正確使用換行

不OK, 換行規則不對(JSONL遇到換行就是要一筆新的資料)

{
  "arr": ["A", "B", "C"]
}

 

OK

{ "arr": ["A", "B", "C"] }

 

呈現多筆的方式錯誤

不OK(使用了標準JSON多筆資料格式)

[
  { "element": "A"},
  { "element": "B"}
]

 

OK

{ "element": "A"}
{ "element": "B"}

 

總結

BigQuery Omni雖然可直接讀取S3很方便

但限制不少

適合的情境比較偏向不會太巨量的資料

在這種情況下使用BQ直接存取第三方的檔案的確是一種不錯的選擇