ציון עמודות בתוך עמודות ועמודות חוזרות בסכימות של טבלאות

בדף הזה מוסבר איך להגדיר סכימת טבלה עם עמודות מקוננות וחוזרות ב-BigQuery. סקירה כללית של סכימות טבלאות זמינה במאמר הגדרת סכימה.

הגדרת עמודות משניות ועמודות חוזרות

כדי ליצור עמודה עם נתונים מקוננים, מגדירים את סוג הנתונים של העמודה ל-RECORD בסכימה. אפשר לגשת אל RECORD כאל סוג STRUCT ב-GoogleSQL. STRUCT הוא מאגר של שדות מסודרים.

כדי ליצור עמודה עם נתונים שחוזרים על עצמם, מגדירים את הסוג של העמודה ל-REPEATED בסכימה. אפשר לגשת לשדה חוזר כסוג ARRAY ב-GoogleSQL.

עמודה מסוג RECORD יכולה להיות במצב REPEATED, שמיוצג כמערך של סוגי STRUCT. בנוסף, שדה בתוך רשומה יכול לחזור על עצמו, והוא מיוצג כ-STRUCT שמכיל ARRAY. מערך לא יכול להכיל מערך אחר באופן ישיר. מידע נוסף זמין במאמר בנושא הצהרה על סוג ARRAY.

מגבלות

סכימות מקוננות וחוזרות כפופות למגבלות הבאות:

סכימה לא יכולה להכיל יותר מ-15 רמות של סוגי RECORD מוטמעים.

עמודות מסוג

יכולות להכיל סוגים מוטמעים של RECORD, שנקראים גם רשומות צאצא.RECORD המגבלה המקסימלית של עומק הקינון היא 15 רמות. המגבלה הזו לא תלויה בכך שRECORD הם סקלריים או מבוססי מערך (חוזרים).

הסוג RECORD לא תואם ל-UNION,‏ INTERSECT,‏ EXCEPT DISTINCT ו-SELECT DISTINCT.

סכימה לדוגמה

בדוגמה הבאה מוצגים נתונים לדוגמה שחוזרים על עצמם ונתונים מקוננים. הטבלה הזו מכילה מידע על אנשים. הוא כולל את השדות הבאים:

• id
• first_name
• last_name
• dob (תאריך לידה)
• ‫addresses (שדה בתוך שדה ושדה חוזר)
  • addresses.status (נוכחי או קודם)
  • addresses.address
  • addresses.city
  • addresses.state
  • addresses.zip
  • ‫addresses.numberOfYears (מספר השנים בכתובת)

קובץ נתוני ה-JSON ייראה כך. שימו לב שהעמודה addresses מכילה מערך של ערכים (מסומן על ידי [ ]). הכתובות הרבות במערך הן הנתונים שחוזרים על עצמם. השדות הרבים בכל כתובת הם הנתונים המקוננים.

{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","addresses":[{"status":"current","address":"123 First Avenue","city":"Seattle","state":"WA","zip":"11111","numberOfYears":"1"},{"status":"previous","address":"456 Main Street","city":"Portland","state":"OR","zip":"22222","numberOfYears":"5"}]}
{"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-10-16","addresses":[{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"},{"status":"previous","address":"321 Main Street","city":"Hoboken","state":"NJ","zip":"44444","numberOfYears":"3"}]}

הסכימה של הטבלה הזו נראית כך:

[
    {
        "name": "id",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "first_name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "last_name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "dob",
        "type": "DATE",
        "mode": "NULLABLE"
    },
    {
        "name": "addresses",
        "type": "RECORD",
        "mode": "REPEATED",
        "fields": [
            {
                "name": "status",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "address",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "city",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "state",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "zip",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "numberOfYears",
                "type": "STRING",
                "mode": "NULLABLE"
            }
        ]
    }
]

ציון העמודות המקוננות והחוזרות בדוגמה

כדי ליצור טבלה חדשה עם העמודות הקודמות שכוללות נתונים מקוננים וחוזרים, בוחרים באחת מהאפשרויות הבאות:

המסוף

מציינים את העמודה addresses המשנית והחוזרת:

1. נכנסים לדף BigQuery במסוף Google Cloud .

   כניסה ל-BigQuery

2. בחלונית הימנית, לוחצים על כלי הניתוחים:

   

   אם החלונית הימנית לא מוצגת, לוחצים על הרחבת החלונית הימנית כדי לפתוח אותה.

3. בחלונית Explorer, מרחיבים את הפרויקט, לוחצים על Datasets ואז בוחרים מערך נתונים.

4. בחלונית הפרטים, לוחצים על יצירת טבלה.

5. בדף Create table, מציינים את הפרטים הבאים:

   • בקטע מקור, בשדה יצירת טבלה מ, בוחרים באפשרות טבלה ריקה.

   • בקטע יעד, מציינים את השדות הבאים:

     • בקטע Dataset (מערך נתונים), בוחרים את מערך הנתונים שבו רוצים ליצור את הטבלה.
     • בשדה Table (טבלה), מזינים את שם הטבלה שרוצים ליצור.

   • בקטע סכימה, לוחצים על הוספת שדה ומזינים את סכימת הטבלה הבאה:

     • בשדה שם השדה, מזינים addresses.
     • בשדה Type, בוחרים באפשרות RECORD.
     • בקטע Mode (מצב), בוחרים באפשרות REPEATED (חוזר).

     • מציינים את השדות הבאים לשדה מקונן:

       • בשדה Field name, מזינים status.
       • בשדה Type, בוחרים באפשרות STRING.
       • בשדה Mode (מצב), משאירים את הערך NULLABLE (ניתן לאיפוס).

       • לוחצים על הוספת שדה כדי להוסיף את השדות הבאים:

         שם השדה	סוג	מצב
         address	STRING	NULLABLE
         city	STRING	NULLABLE
         state	STRING	NULLABLE
         zip	STRING	NULLABLE
         numberOfYears	STRING	NULLABLE

       לחלופין, לוחצים על Edit as text ומציינים את הסכימה כמערך JSON.

SQL

משתמשים בהצהרה CREATE TABLE. מציינים את הסכימה באמצעות האפשרות column:

1. במסוף Google Cloud , עוברים לדף BigQuery.

   כניסה ל-BigQuery

2. מזינים את ההצהרה הבאה בעורך השאילתות:

   CREATE TABLE IF NOT EXISTS mydataset.mytable (
     id STRING,
     first_name STRING,
     last_name STRING,
     dob DATE,
     addresses
       ARRAY<
         STRUCT<
           status STRING,
           address STRING,
           city STRING,
           state STRING,
           zip STRING,
           numberOfYears STRING>>
   ) OPTIONS (
       description = 'Example name and addresses table');

3. לוחצים על הפעלה.

מידע נוסף על הרצת שאילתות זמין במאמר הרצת שאילתה אינטראקטיבית.

BQ

כדי לציין את העמודה addresses המקוננת והחוזרת בקובץ סכימת JSON, צריך ליצור קובץ חדש באמצעות עורך טקסט. מדביקים את ההגדרה של סכימת הדוגמה שמוצגת למעלה.

אחרי שיוצרים את קובץ סכימת ה-JSON, אפשר לספק אותו באמצעות כלי שורת הפקודה של BigQuery. מידע נוסף זמין במאמר בנושא שימוש בקובץ סכימת JSON.

Go

לפני שמנסים את הדוגמה הזו, צריך לפעול לפי Goהוראות ההגדרה שבמדריך למתחילים של BigQuery באמצעות ספריות לקוח. מידע נוסף מופיע במאמרי העזרה של BigQuery Go API.

כדי לבצע אימות ב-BigQuery, צריך להגדיר את Application Default Credentials. מידע נוסף זמין במאמר הגדרת אימות לספריות לקוח.

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
)

// createTableComplexSchema demonstrates creating a BigQuery table and specifying a complex schema that includes
// an array of Struct types.
func createTableComplexSchema(w io.Writer, projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	sampleSchema := bigquery.Schema{
		{Name: "id", Type: bigquery.StringFieldType},
		{Name: "first_name", Type: bigquery.StringFieldType},
		{Name: "last_name", Type: bigquery.StringFieldType},
		{Name: "dob", Type: bigquery.DateFieldType},
		{Name: "addresses",
			Type:     bigquery.RecordFieldType,
			Repeated: true,
			Schema: bigquery.Schema{
				{Name: "status", Type: bigquery.StringFieldType},
				{Name: "address", Type: bigquery.StringFieldType},
				{Name: "city", Type: bigquery.StringFieldType},
				{Name: "state", Type: bigquery.StringFieldType},
				{Name: "zip", Type: bigquery.StringFieldType},
				{Name: "numberOfYears", Type: bigquery.StringFieldType},
			}},
	}

	metaData := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, metaData); err != nil {
		return err
	}
	fmt.Fprintf(w, "created table %s\n", tableRef.FullyQualifiedName())
	return nil
}

Java

לפני שמנסים את הדוגמה הזו, צריך לפעול לפי Javaהוראות ההגדרה שבמדריך למתחילים של BigQuery באמצעות ספריות לקוח. מידע נוסף מופיע במאמרי העזרה של BigQuery Java API.

כדי לבצע אימות ב-BigQuery, צריך להגדיר את Application Default Credentials. מידע נוסף זמין במאמר הגדרת אימות לספריות לקוח.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.Field.Mode;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

public class NestedRepeatedSchema {

  public static void runNestedRepeatedSchema() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    createTableWithNestedRepeatedSchema(datasetName, tableName);
  }

  public static void createTableWithNestedRepeatedSchema(String datasetName, String tableName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);

      Schema schema =
          Schema.of(
              Field.of("id", StandardSQLTypeName.STRING),
              Field.of("first_name", StandardSQLTypeName.STRING),
              Field.of("last_name", StandardSQLTypeName.STRING),
              Field.of("dob", StandardSQLTypeName.DATE),
              // create the nested and repeated field
              Field.newBuilder(
                      "addresses",
                      StandardSQLTypeName.STRUCT,
                      Field.of("status", StandardSQLTypeName.STRING),
                      Field.of("address", StandardSQLTypeName.STRING),
                      Field.of("city", StandardSQLTypeName.STRING),
                      Field.of("state", StandardSQLTypeName.STRING),
                      Field.of("zip", StandardSQLTypeName.STRING),
                      Field.of("numberOfYears", StandardSQLTypeName.STRING))
                  .setMode(Mode.REPEATED)
                  .build());

      TableDefinition tableDefinition = StandardTableDefinition.of(schema);
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Table with nested and repeated schema created successfully");
    } catch (BigQueryException e) {
      System.out.println("Table was not created. \n" + e.toString());
    }
  }
}

Node.js

לפני שמנסים את הדוגמה הזו, צריך לפעול לפי Node.jsהוראות ההגדרה שבמדריך למתחילים של BigQuery באמצעות ספריות לקוח. מידע נוסף מופיע במאמרי העזרה של BigQuery Node.js API.

כדי לבצע אימות ב-BigQuery, צריך להגדיר את Application Default Credentials. מידע נוסף זמין במאמר הגדרת אימות לספריות לקוח.

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function nestedRepeatedSchema() {
  // Creates a new table named "my_table" in "my_dataset"
  // with nested and repeated columns in schema.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";
  // const schema = [
  //   {name: 'Name', type: 'STRING', mode: 'REQUIRED'},
  //   {
  //     name: 'Addresses',
  //     type: 'RECORD',
  //     mode: 'REPEATED',
  //     fields: [
  //       {name: 'Address', type: 'STRING'},
  //       {name: 'City', type: 'STRING'},
  //       {name: 'State', type: 'STRING'},
  //       {name: 'Zip', type: 'STRING'},
  //     ],
  //   },
  // ];

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    location: 'US',
  };

  // Create a new table in the dataset
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);
}

Python

לפני שמנסים את הדוגמה הזו, צריך לפעול לפי Pythonהוראות ההגדרה שבמדריך למתחילים של BigQuery באמצעות ספריות לקוח. מידע נוסף מופיע במאמרי העזרה של BigQuery Python API.

כדי לבצע אימות ב-BigQuery, צריך להגדיר את Application Default Credentials. מידע נוסף זמין במאמר הגדרת אימות לספריות לקוח.

from google.cloud import bigquery

client = bigquery.Client()

# TODO(dev): Change table_id to the full name of the table you want to create.
table_id = "your-project.your_dataset.your_table_name"

schema = [
    bigquery.SchemaField("id", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("first_name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("last_name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("dob", "DATE", mode="NULLABLE"),
    bigquery.SchemaField(
        "addresses",
        "RECORD",
        mode="REPEATED",
        fields=[
            bigquery.SchemaField("status", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("address", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("city", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("state", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("zip", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("numberOfYears", "STRING", mode="NULLABLE"),
        ],
    ),
]
table = bigquery.Table(table_id, schema=schema)
table = client.create_table(table)  # API request

print(f"Created table {table.project}.{table.dataset_id}.{table.table_id}.")

הוספת נתונים בעמודות מקוננות בדוגמה

אפשר להשתמש בשאילתות הבאות כדי להוסיף רשומות נתונים מקוננות לטבלאות שיש בהן עמודות עם סוג הנתונים RECORD.

דוגמה 1

INSERT INTO mydataset.mytable (id,
first_name,
last_name,
dob,
addresses) values ("1","Johnny","Dawn","1969-01-22",
    ARRAY<
      STRUCT<
        status STRING,
        address STRING,
        city STRING,
        state STRING,
        zip STRING,
        numberOfYears STRING>>
      [("current","123 First Avenue","Seattle","WA","11111","1")])

דוגמה 2

INSERT INTO mydataset.mytable (id,
first_name,
last_name,
dob,
addresses) values ("1","Johnny","Dawn","1969-01-22",[("current","123 First Avenue","Seattle","WA","11111","1")])

שאילתה של עמודות משניות ועמודות חוזרות

כדי לבחור את הערך של ARRAY במיקום ספציפי, משתמשים באופרטור של אינדקס במערך. כדי לגשת לאלמנטים ב-STRUCT, משתמשים באופרטור הנקודה. בדוגמה הבאה נבחרו השם הפרטי, שם המשפחה והכתובת הראשונה שמופיעים בשדה addresses:

SELECT
  first_name,
  last_name,
  addresses[offset(0)].address
FROM
  mydataset.mytable;

התוצאה היא:

+------------+-----------+------------------+
| first_name | last_name | address          |
+------------+-----------+------------------+
| John       | Doe       | 123 First Avenue |
| Jane       | Doe       | 789 Any Avenue   |
+------------+-----------+------------------+

כדי לחלץ את כל הרכיבים של ARRAY, משתמשים באופרטור UNNEST עם CROSS JOIN. בדוגמה הבאה נבחרים השם הפרטי, שם המשפחה, הכתובת והמדינה של כל הכתובות שלא נמצאות בניו יורק:

SELECT
  first_name,
  last_name,
  a.address,
  a.state
FROM
  mydataset.mytable CROSS JOIN UNNEST(addresses) AS a
WHERE
  a.state != 'NY';

התוצאה היא:

+------------+-----------+------------------+-------+
| first_name | last_name | address          | state |
+------------+-----------+------------------+-------+
| John       | Doe       | 123 First Avenue | WA    |
| John       | Doe       | 456 Main Street  | OR    |
| Jane       | Doe       | 321 Main Street  | NJ    |
+------------+-----------+------------------+-------+

שינוי עמודות בתוך עמודות ועמודות חוזרות

אחרי שמוסיפים עמודה מקוננת או עמודה מקוננת וחוזרת להגדרת הסכימה של טבלה, אפשר לשנות את העמודה כמו כל סוג אחר של עמודה. יש כמה שינויים בסכימה שנתמכים באופן טבעי ב-BigQuery, כמו הוספה של שדה חדש מקונן לרשומה או שינוי המצב של שדה מקונן. מידע נוסף זמין במאמר בנושא שינוי סכימות של טבלאות.

מתי כדאי להשתמש בעמודות מקוננות ובעמודות חוזרות

הביצועים של BigQuery הכי טובים כשהנתונים לא מנורמלים. במקום לשמור על סכימה רלציונית כמו סכימת כוכב או סכימת פתית שלג, כדאי לבצע דה-נורמליזציה של הנתונים ולהשתמש בעמודות מקוננות וחוזרות. עמודות מקוננות וחוזרות יכולות לשמור על קשרים בלי להשפיע על הביצועים, כמו שקורה כששומרים על סכימה יחסית (מנורמלת).

לדוגמה, במסד נתונים רלציוני שמשמש למעקב אחרי ספרים בספרייה, סביר להניח שכל פרטי המחבר יישמרו בטבלה נפרדת. מפתח כמו author_id ישמש לקישור הספר למחברים.

ב-BigQuery, אפשר לשמור על הקשר בין הספר לבין המחבר בלי ליצור טבלת מחברים נפרדת. במקום זאת, יוצרים עמוד של מחבר ומקננים בו שדות כמו שם פרטי, שם משפחה, תאריך לידה וכו'. אם לספר יש כמה מחברים, אפשר לחזור על העמודה המקוננת של המחבר.

נניח שיש לכם את הטבלה הבאה mydataset.books:

+------------------+------------+-----------+
| title            | author_ids | num_pages |
+------------------+------------+-----------+
| Example Book One | [123, 789] | 487       |
| Example Book Two | [456]      | 89        |
+------------------+------------+-----------+

בנוסף, יש לך את הטבלה הבאה, mydataset.authors, עם מידע מלא על כל מזהה מחבר:

+-----------+-------------+---------------+
| author_id | author_name | date_of_birth |
+-----------+-------------+---------------+
| 123       | Alex        | 01-01-1960    |
| 456       | Rosario     | 01-01-1970    |
| 789       | Kim         | 01-01-1980    |
+-----------+-------------+---------------+

אם הטבלאות גדולות, יכול להיות שחיבור שלהן באופן קבוע ידרוש הרבה משאבים. בהתאם למצב, יכול להיות שכדאי ליצור טבלה אחת עם כל המידע:

CREATE TABLE mydataset.denormalized_books(
  title STRING,
  authors ARRAY<STRUCT<id INT64, name STRING, date_of_birth STRING>>,
  num_pages INT64)
AS (
  SELECT
    title,
    ARRAY_AGG(STRUCT(author_id, author_name, date_of_birth)) AS authors,
    ANY_VALUE(num_pages)
  FROM
    mydataset.books,
    UNNEST(author_ids) id
  JOIN
    mydataset.authors
    ON
      id = author_id
  GROUP BY
    title
);

הטבלה שמתקבלת נראית כך:

+------------------+-------------------------------+-----------+
| title            | authors                       | num_pages |
+------------------+-------------------------------+-----------+
| Example Book One | [{123, Alex, 01-01-1960},     | 487       |
|                  |  {789, Kim, 01-01-1980}]      |           |
| Example Book Two | [{456, Rosario, 01-01-1970}]  | 89        |
+------------------+-------------------------------+-----------+

‫BigQuery תומך בטעינת נתונים מקוננים וחוזרים מפורמטים של מקורות שתומכים בסכימות מבוססות-אובייקטים, כמו קובצי JSON, קובצי Avro, קובצי ייצוא של Firestore וקובצי ייצוא של Datastore.

ביטול כפילויות של רשומות כפולות בטבלה

השאילתה הבאה משתמשת בפונקציה row_number() כדי לזהות רשומות כפולות עם אותם ערכים עבור last_name ו-first_name בדוגמאות שבהן נעשה שימוש, וממיינת אותן לפי dob:

CREATE OR REPLACE TABLE mydataset.mytable AS (
  SELECT * except(row_num) FROM (
    SELECT *,
    row_number() over (partition by last_name, first_name order by dob) row_num
    FROM
    mydataset.mytable) temp_table
  WHERE row_num=1
)

אבטחת טבלאות

כדי לשלוט בגישה לטבלאות ב-BigQuery, אפשר לעיין במאמר בנושא שליטה בגישה למשאבים באמצעות IAM.

המאמרים הבאים

• כדי להוסיף ולעדכן שורות עם עמודות מקוננות וחוזרות, אפשר לעיין בתחביר של שפת טיפול בנתונים (DML).