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

עמודות חוזרות ועמודות בתצוגת עץ מאפשרות לאחסן נתונים קשורים, כמו ספר והמחברים שלו, בטבלה אחת ב-BigQuery במקום בטבלאות נפרדות שצריך לחבר. מכיוון ש-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. בחלונית השמאלית, לוחצים על Explorer:

   

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

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

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

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

   • בקטע Source, בשדה Create table from בוחרים באפשרות Empty table.

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

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

   • בקטע Schema, לוחצים על Add field ומזינים את סכימת הטבלה הבאה:

     • בשם השדה, מזינים addresses.
     • בשדה Type, בוחרים באפשרות RECORD.
     • בשדה Mode, בוחרים באפשרות REPEATED.

     • בשדה בתצוגת עץ, צריך לציין את השדות הבאים:

       • בשדה שם השדה, מזינים 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. נכנסים לדף BigQuery במסוף Google Cloud .

   כניסה ל-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 במדריך למתחילים של 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.

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

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