在 Cloud Storage 中查詢
本指南說明資料在 Cloud Storage 中的儲存方式,包括原始格式和處理後的格式。
原始資料
Manufacturing Data Engine (MDE) 會以原始格式儲存收到的所有訊息,不會進行處理。這項功能可用於封存資料,也能在發生設定或系統錯誤時重新處理資料。
原始資料會以 AVRO 格式儲存,並使用下列結構定義:
{
"type" : "record",
"name" : "AvroPubsubMessageRecord",
"namespace" : "com.google.cloud.industry.manufacturing.sfp.datalake.core",
"fields" : [ {
"name" : "attributes",
"type" : {
"type" : "map",
"values" : "string"
}
}, {
"name" : "message",
"type" : {
"type" : "bytes",
"java-class" : "[B"
}
}, {
"name" : "messageId",
"type" : "string"
}, {
"name" : "timestamp",
"type" : "long"
} ]
結構定義由下列值組成:
- attributes:儲存 Pub/Sub 訊息屬性對應。
- message:儲存從 Pub/Sub 主題收到的原始訊息
input-messages。 - messageId:Pub/Sub 接收訊息時設定的 ID,會寫入所有接收器,以便追蹤資料來源。
- 時間戳記:Pub/Sub 接收訊息的時間。
資料預設會儲存在名為 <project-id>-raw 的 Cloud Storage bucket 中。然後在版本資料夾下方,在本例中為 1.3。
然後使用日期分區,以 dt=YYYY-MM-DD 格式依日期分隔檔案。
訊息會以 10 分鐘為間隔分組,每個間隔會寫入 10 個檔案,這些資料會用於產生檔案名稱。以下指令碼範例說明:
gs://<project-id>-raw/v1.3/dt=2023-08-08/gcsoutput2023-08-08T09:20:00.000Z-2023-08-08T09:30:00.000Z-pane-0-last-00-of-10avro
gs://<project-id>-raw/v1.3/dt=2023-08-08/gcsoutput2023-08-08T09:20:00.000Z-2023-08-08T09:30:00.000Z-pane-0-last-01-of-10avro
gs://<project-id>-raw/v1.3/dt=2023-08-08/gcsoutput2023-08-08T09:20:00.000Z-2023-08-08T09:30:00.000Z-pane-0-last-02-of-10avro
gs://<project-id>-raw/v1.3/dt=2023-08-08/gcsoutput2023-08-08T09:20:00.000Z-2023-08-08T09:30:00.000Z-pane-0-last-03-of-10avro
gs://<project-id>-raw/v1.3/dt=2023-08-08/gcsoutput2023-08-08T09:20:00.000Z-2023-08-08T09:30:00.000Z-pane-0-last-04-of-10avro
gs://<project-id>-raw/v1.3/dt=2023-08-08/gcsoutput2023-08-08T09:20:00.000Z-2023-08-08T09:30:00.000Z-pane-0-last-05-of-10avro
gs://<project-id>-raw/v1.3/dt=2023-08-08/gcsoutput2023-08-08T09:20:00.000Z-2023-08-08T09:30:00.000Z-pane-0-last-06-of-10avro
gs://<project-id>-raw/v1.3/dt=2023-08-08/gcsoutput2023-08-08T09:20:00.000Z-2023-08-08T09:30:00.000Z-pane-0-last-07-of-10avro
gs://<project-id>-raw/v1.3/dt=2023-08-08/gcsoutput2023-08-08T09:20:00.000Z-2023-08-08T09:30:00.000Z-pane-0-last-08-of-10avro
gs://<project-id>-raw/v1.3/dt=2023-08-08/gcsoutput2023-08-08T09:20:00.000Z-2023-08-08T09:30:00.000Z-pane-0-last-09-of-10avro
讀取 BigQuery
以下各節說明從 BigQuery 讀取資料的程序。
已處理的資料
MDE 處理資料後,可以設定將資料儲存在 Cloud Storage 中。
根據預設,資料會儲存在名為 <project-id>-gcs-ingestion 的 bucket 中。
接著,系統會將檔案儲存在版本資料夾 (本例為 v1.3) 下,然後將每個類型儲存在各自的資料夾 (default-discrete-records,
default-numeric-records, etc) 中,最後使用日期分割功能,以 dt=YYYY-MM-DD 格式依日期分隔檔案。
與原始資料一樣,訊息會以 10 分鐘為間隔分組,每個間隔會寫入 10 個檔案,這些資料會用於產生檔名,例如:
gs://<project-id>-gcs-ingestion/v1.3/default-discrete-records/dt=2023-08-08/gcsoutput2023-08-08T09:40:00.000Z-2023-08-08T09:50:00.000Z-00000-of-00010.avro
gs://<project-id>-gcs-ingestion/v1.3/default-discrete-records/dt=2023-08-08/gcsoutput2023-08-08T09:40:00.000Z-2023-08-08T09:50:00.000Z-00001-of-00010.avro
gs://<project-id>-gcs-ingestion/v1.3/default-discrete-records/dt=2023-08-08/gcsoutput2023-08-08T09:40:00.000Z-2023-08-08T09:50:00.000Z-00002-of-00010.avro
gs://<project-id>-gcs-ingestion/v1.3/default-discrete-records/dt=2023-08-08/gcsoutput2023-08-08T09:40:00.000Z-2023-08-08T09:50:00.000Z-00003-of-00010.avro
gs://<project-id>-gcs-ingestion/v1.3/default-discrete-records/dt=2023-08-08/gcsoutput2023-08-08T09:40:00.000Z-2023-08-08T09:50:00.000Z-00004-of-00010.avro
gs://<project-id>-gcs-ingestion/v1.3/default-discrete-records/dt=2023-08-08/gcsoutput2023-08-08T09:40:00.000Z-2023-08-08T09:50:00.000Z-00005-of-00010.avro
gs://<project-id>-gcs-ingestion/v1.3/default-discrete-records/dt=2023-08-08/gcsoutput2023-08-08T09:40:00.000Z-2023-08-08T09:50:00.000Z-00006-of-00010.avro
gs://<project-id>-gcs-ingestion/v1.3/default-discrete-records/dt=2023-08-08/gcsoutput2023-08-08T09:40:00.000Z-2023-08-08T09:50:00.000Z-00007-of-00010.avro
gs://<project-id>-gcs-ingestion/v1.3/default-discrete-records/dt=2023-08-08/gcsoutput2023-08-08T09:40:00.000Z-2023-08-08T09:50:00.000Z-00008-of-00010.avro
gs://<project-id>-gcs-ingestion/v1.3/default-discrete-records/dt=2023-08-08/gcsoutput2023-08-08T09:40:00.000Z-2023-08-08T09:50:00.000Z-00009-of-00010.avro
每個原型都有略微不同的架構,因此每種原型都會使用對應的架構。請參閱以下例子:
NumericDataSeries 原型結構定義:
{
"type": "record",
"namespace": "com.google.cloud.industry.manufacturing.sfp.datalake.storage.gcs",
"name": "NumericDataSeriesGCSObject",
"fields": [
{
"name": "id",
"type": "string",
"doc": "Unique record id"
},
{
"name": "tag_name",
"type": "string",
"doc": "Name of the tag"
},
{
"name": "type_name",
"type": "string"
},
{
"name": "type_version",
"type": "int"
},
{
"name": "embedded_metadata",
"type": ["null", "string"],
"default": null
},
{
"name": "materialized_cloud_metadata",
"type": ["null", "string"],
"default": null
},
{
"name": "cloud_metadata_ref",
"type": ["null", "string"],
"default": null
},
{
"name": "source_message_id",
"type": "string"
},
{
"name": "event_timestamp",
"type": {
"type": "long",
"logicalType": "timestamp-millis"
}
},
{
"name": "value",
"type": "double"
}
]
}
DiscreteDataSeries 原型結構定義:
{
"type": "record",
"name": "DiscreteDataSeriesGCSObject",
"namespace": "com.google.cloud.industry.manufacturing.sfp.datalake.storage.gcs",
"fields": [
{
"name": "id",
"type": {
"type": "string",
"avro.java.string": "String"
},
"doc": "Unique record id"
},
{
"name": "tag_name",
"type": {
"type": "string",
"avro.java.string": "String"
},
"doc": "Name of the tag"
},
{
"name": "type_name",
"type": {
"type": "string",
"avro.java.string": "String"
}
},
{
"name": "type_version",
"type": "int"
},
{
"name": "embedded_metadata",
"type": [
"null",
{
"type": "string",
"avro.java.string": "String"
}
],
"default": null
},
{
"name": "materialized_cloud_metadata",
"type": [
"null",
{
"type": "string",
"avro.java.string": "String"
}
],
"default": null
},
{
"name": "cloud_metadata_ref",
"type": [
"null",
{
"type": "string",
"avro.java.string": "String"
}
],
"default": null
},
{
"name": "source_message_id",
"type": {
"type": "string",
"avro.java.string": "String"
}
},
{
"name": "event_timestamp",
"type": {
"type": "long",
"logicalType": "timestamp-millis"
}
},
{
"name": "data",
"type": {
"type": "string",
"avro.java.string": "String"
}
}
]
}
ContinuousDataSeries 原型結構定義:
{
"type": "record",
"namespace": "com.google.cloud.industry.manufacturing.sfp.datalake.storage.gcs",
"name": "ContinuousDataSeriesGCSObject",
"fields": [
{
"name": "id",
"type": "string",
"doc": "Unique record id"
},
{
"name": "tag_name",
"type": "string",
"doc": "Name of the tag"
},
{
"name": "type_name",
"type": "string"
},
{
"name": "type_version",
"type": "int"
},
{
"name": "embedded_metadata",
"type": ["null", "string"],
"default": null
},
{
"name": "materialized_cloud_metadata",
"type": ["null", "string"],
"default": null
},
{
"name": "cloud_metadata_ref",
"type": ["null", "string"],
"default": null
},
{
"name": "source_message_id",
"type": "string"
},
{
"name": "event_timestamp_start",
"type": {
"type": "long",
"logicalType": "timestamp-millis"
}
},
{
"name": "event_timestamp_end",
"type": {
"type": "long",
"logicalType": "timestamp-millis"
}
},
{
"name": "data",
"type": "string"
},
{
"name": "duration",
"type": "long"
}
]
}