מבוא ל-SQL ב-BigQuery

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

‫GoogleSQL היא שפת שאילתות מובנית (SQL) תואמת ANSI, שתומכת בסוגי ההצהרות הבאים:

דיאלקטים של SQL ב-BigQuery

‫BigQuery תומך בדיאלקט GoogleSQL, שהוא הדיאלקט המומלץ לכל הפרויקטים החדשים. אפשר גם להשתמש בניב SQL מדור קודם, עם כמה מגבלות. מומלץ לעבור מ-SQL מדור קודם ל-GoogleSQL.

שינוי הניב שמוגדר כברירת מחדל

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

המסוף

ניב ברירת המחדל של מסוף Google Cloud הוא GoogleSQL. כדי לשנות את הניב ל-SQL מדור קודם:

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

    כניסה ל-BigQuery

  2. בעורך השאילתות, לוחצים על Edit > Query settings.

  3. בקטע Advanced options, בשדה SQL dialect לוחצים על Legacy ואז על Save. ההגדרה הזו קובעת את האפשרות של SQL מדור קודם בשאילתה הזו. כשלוחצים על add_box שאילתת SQL כדי ליצור שאילתה חדשה, צריך לבחור שוב באפשרות של SQL מדור קודם.

SQL

דיאלקט ברירת המחדל של SQL הוא GoogleSQL. כדי להגדיר את ניב ה-SQL, אפשר לכלול את הקידומת #standardSQL או #legacySQL כחלק מהשאילתה. הקידומות האלה לא תלויות אותיות רישיות, הן חייבות להופיע לפני השאילתה וצריך להפריד ביניהן לבין השאילתה באמצעות תו של מעבר שורה. בדוגמה הבאה, הדיאלקט מוגדר ל-SQL מדור קודם והשאילתה מורצת על מערך הנתונים natality:

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

    כניסה ל-BigQuery

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

    #legacySQL
SELECT
  weight_pounds, state, year, gestation_weeks
FROM
  [bigquery-public-data:samples.natality]
ORDER BY
  weight_pounds DESC
LIMIT
  10;

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

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

BQ

דיאלקט השאילתה שמוגדר כברירת מחדל בכלי שורת הפקודה bq הוא SQL מדור קודם. כדי לעבור לניב GoogleSQL, מוסיפים את הדגל --use_legacy_sql=false או --nouse_legacy_sql להצהרה בשורת הפקודה.

מעבר לניב GoogleSQL

כדי להשתמש בתחביר של GoogleSQL במשימת שאילתה, מגדירים את הפרמטר use_legacy_sql ל-false.

  bq query \
  --use_legacy_sql=false \
  'SELECT
    word
  FROM
    `bigquery-public-data.samples.shakespeare`'

הגדרת GoogleSQL כניב ברירת המחדל

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

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

כדי להגדיר את --use_legacy_sql=false ב-.bigqueryrc:

  1. פותחים את .bigqueryrc בכלי לעריכת טקסט. כברירת מחדל, .bigqueryrc אמור להיות בספריית המשתמש, לדוגמה $HOME/.bigqueryrc.

  2. מוסיפים את הטקסט הבא לקובץ. בדוגמה הזו, GoogleSQL מוגדר כתחביר ברירת המחדל לשאילתות ולפקודה mk (שמשמשת ליצירת תצוגה). אם כבר הגדרתם ערכי ברירת מחדל לדגלי הפקודות query או mk, אתם לא צריכים להוסיף שוב את [query] או [mk].

    [query]
--use_legacy_sql=false
[mk]
--use_legacy_sql=false

  3. שומרים וסוגרים את הקובץ.

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

למידע על הדגלים הזמינים בשורת הפקודה, קראו את חומרי העזר של כלי שורת הפקודה של BigQuery.

C#‎

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

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

כברירת מחדל, ספריית C# ‎ משתמשת ב-GoogleSQL.

מעבר לניב SQL מדור קודם

כדי להשתמש בתחביר SQL מדור קודם במשימת שאילתה, מגדירים את הפרמטר UseLegacySql ל-true.


using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryQueryLegacy
{
    public void QueryLegacy(
        string projectId = "your-project-id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        string query = @"
            SELECT name FROM [bigquery-public-data:usa_names.usa_1910_2013]
            WHERE state = 'TX'
            LIMIT 100";
        BigQueryJob job = client.CreateQueryJob(
            sql: query,
            parameters: null,
            options: new QueryOptions { UseLegacySql = true });
        // Wait for the job to complete.
        job = job.PollUntilCompleted().ThrowOnAnyError();
        // Display the results
        foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
        {
            Console.WriteLine($"{row["name"]}");
        }
    }
}

המשך

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

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

כברירת מחדל, ספריית הלקוח של Go משתמשת ב-GoogleSQL.

מעבר לניב SQL מדור קודם

כדי להשתמש בתחביר של SQL מדור קודם במשימת שאילתה, מגדירים את המאפיין UseLegacySQL בהגדרות השאילתה כ-true.

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
	"google.golang.org/api/iterator"
)

// queryLegacy demonstrates running a query using Legacy SQL.
func queryLegacy(w io.Writer, projectID, sqlString string) error {
	// projectID := "my-project-id"
	// sqlString = "SELECT 3 as somenum"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %w", err)
	}
	defer client.Close()

	q := client.Query(sqlString)
	q.UseLegacySQL = true

	// Run the query and process the returned row iterator.
	it, err := q.Read(ctx)
	if err != nil {
		return fmt.Errorf("query.Read(): %w", err)
	}
	for {
		var row []bigquery.Value
		err := it.Next(&row)
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Fprintln(w, row)
	}
	return nil
}

Java

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

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

כברירת מחדל, ספריית הלקוח של Java משתמשת ב-GoogleSQL.

מעבר לניב SQL מדור קודם

כדי להשתמש בתחביר SQL מדור קודם במשימת שאילתה, מגדירים את הפרמטר useLegacySql לערך true.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableResult;

public class RunLegacyQuery {

  public static void main(String[] args) {
    runLegacyQuery();
  }

  public static void runLegacyQuery() {
    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();

      // To use legacy SQL syntax, set useLegacySql to true.
      String query =
          "SELECT corpus FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus;";
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query).setUseLegacySql(true).build();

      // Execute the query.
      TableResult result = bigquery.query(queryConfig);

      // Print the results.
      result.iterateAll().forEach(rows -> rows.forEach(row -> System.out.println(row.getValue())));

      System.out.println("Legacy query ran successfully");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Legacy query did not run \n" + e.toString());
    }
  }
}

Node.js

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

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

כברירת מחדל, ספריית הלקוח של Node.js משתמשת ב-GoogleSQL.

מעבר לניב SQL מדור קודם

כדי להשתמש בתחביר SQL מדור קודם במשימת שאילתה, מגדירים את הפרמטר useLegacySql לערך true.

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

async function queryLegacy() {
  // Queries the U.S. given names dataset for the state of Texas using legacy SQL.

  const query =
    'SELECT word FROM [bigquery-public-data:samples.shakespeare] LIMIT 10;';

  // For all options, see https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query
  const options = {
    query: query,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
    useLegacySql: true,
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  rows.forEach(row => console.log(row));
}

PHP

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

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

כברירת מחדל, ספריית הלקוח של PHP משתמשת ב-GoogleSQL.

מעבר לניב SQL מדור קודם

כדי להשתמש בתחביר SQL מדור קודם במשימת שאילתה, מגדירים את הפרמטר useLegacySql לערך true.

use Google\Cloud\BigQuery\BigQueryClient;

/**
 * Query using legacy sql
 *
 * @param string $projectId The project Id of your Google Cloud Project.
 */
function query_legacy(string $projectId): void
{
    $query = 'SELECT corpus FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus';

    $bigQuery = new BigQueryClient([
      'projectId' => $projectId,
    ]);
    $jobConfig = $bigQuery->query($query)->useLegacySql(true);

    $queryResults = $bigQuery->runQuery($jobConfig);

    $i = 0;
    foreach ($queryResults as $row) {
        printf('--- Row %s ---' . PHP_EOL, ++$i);
        foreach ($row as $column => $value) {
            printf('%s: %s' . PHP_EOL, $column, json_encode($value));
        }
    }
    printf('Found %s row(s)' . PHP_EOL, $i);
}

Python

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

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

כברירת מחדל, ספריית הלקוח של Python משתמשת ב-GoogleSQL.

מעבר לניב SQL מדור קודם

כדי להשתמש בתחביר SQL מדור קודם במשימת שאילתה, מגדירים את הפרמטר use_legacy_sql לערך True.

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

query = (
    "SELECT name FROM [bigquery-public-data:usa_names.usa_1910_2013] "
    'WHERE state = "TX" '
    "LIMIT 100"
)

# Set use_legacy_sql to True to use legacy SQL syntax.
job_config = bigquery.QueryJobConfig(use_legacy_sql=True)

# Start the query and waits for query job to complete, passing in the extra configuration.
results = client.query_and_wait(
    query, job_config=job_config
)  # Make an API request.

print("The query data:")
for row in results:
    print(row)

Ruby

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

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

כברירת מחדל, ספריית הלקוח של Ruby משתמשת ב-GoogleSQL.

מעבר לניב SQL מדור קודם

כדי להשתמש בתחביר SQL מדור קודם במשימת שאילתה, צריך להעביר את האפשרות legacy_sql: true עם השאילתה.

require "google/cloud/bigquery"

def query_legacy
  bigquery = Google::Cloud::Bigquery.new
  sql = "SELECT name FROM [bigquery-public-data:usa_names.usa_1910_2013] " \
        "WHERE state = 'TX' " \
        "LIMIT 100"

  results = bigquery.query sql, legacy_sql: true do |config|
    # Location must match that of the dataset(s) referenced in the query.
    config.location = "US"
  end

  results.each do |row|
    puts row.inspect
  end
end

פתרון בעיות בשאילתות

למידע על פתרון בעיות בהרצת שאילתות, קראו את המאמר פתרון בעיות בשאילתות.

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