Membuat kueri di Cloud Storage

Panduan ini menjelaskan cara data disimpan di Cloud Storage, baik dalam format mentah maupun yang sudah diproses.

Data mentah

Manufacturing Data Engine (MDE) menyimpan semua pesan yang diterima dalam format mentah tanpa diproses. Hal ini berguna baik untuk alasan pengarsipan, maupun untuk memproses ulang data jika terjadi kesalahan konfigurasi atau sistem.

Data mentah disimpan dalam format AVRO menggunakan skema berikut:

{
  "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"
  } ]

Skema ini terdiri dari nilai-nilai berikut:

• attributes: Menyimpan peta atribut pesan Pub/Sub.
• message: Menyimpan pesan mentah yang diterima di topik Pub/Sub input-messages.
• messageId: ID yang ditetapkan oleh Pub/Sub saat menerima pesan, ID ini ditulis ke semua sink untuk mengetahui asal data.
• timestamp: Saat pesan diterima oleh Pub/Sub.

Data disimpan secara default di bucket Cloud Storage yang bernama <project-id>-raw. Kemudian, di bawah folder untuk versi, dalam hal ini 1.3.

Kemudian, partisi tanggal digunakan untuk memisahkan file menurut hari dengan format dt=YYYY-MM-DD.

Pesan dikelompokkan dalam periode 10 menit dan 10 file ditulis di setiap periode, data ini digunakan untuk menghasilkan nama file. Skrip berikut menunjukkan contohnya:

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

Membaca dari BigQuery

Bagian berikut menjelaskan prosedur pembacaan data dari BigQuery.

Data yang diproses

Setelah diproses oleh MDE, data dapat dikonfigurasi untuk disimpan di Cloud Storage.

Secara default, data disimpan dalam bucket bernama <project-id>-gcs-ingestion. Kemudian, file tersebut disimpan dalam folder untuk versi, dalam hal ini v1.3, lalu setiap jenis disimpan dalam foldernya sendiri (default-discrete-records, default-numeric-records, etc) dan terakhir, partisi tanggal digunakan untuk memisahkan file menurut hari dengan format dt=YYYY-MM-DD.

Seperti halnya Data mentah, pesan dikelompokkan dalam periode 10 menit dan 10 file ditulis di setiap periode. Data ini digunakan untuk menghasilkan nama file, yaitu:

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

Ada skema berbeda yang hanya sedikit berbeda untuk setiap Arketipe, sehingga setiap jenis menggunakan skema yang sesuai. Lihat contoh berikut:

Skema arketipe 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"
    }
  ]
}

Skema arketipe 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"
      }
    }
  ]
}

Skema arketipe 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"
    }
  ]
}