שיטות מומלצות לייבוא וייצוא של נתונים

‫MySQL | PostgreSQL | SQL Server

בדף הזה מוסבר על השיטות המומלצות לייבוא וייצוא של נתונים באמצעות Cloud SQL. הוראות מפורטות לייבוא נתונים ל-Cloud SQL מופיעות במאמר ייבוא נתונים. הוראות מפורטות לייצוא נתונים, בין אם הם נמצאים ב-Cloud SQL או במופע שאתם מנהלים, מופיעות במאמר ייצוא נתונים.

שיטות מומלצות לייבוא ולייצוא

ריכזנו כאן כמה שיטות מומלצות שכדאי להביא בחשבון כשמייבאים ומייצאים נתונים:

לא להשתמש בקטגוריות של Cloud Storage עם התכונה 'מגיש הבקשה משלם'
דחיסת נתונים כדי להפחית את העלות.
הפחתת משך הזמן של תהליכי ייבוא וייצוא
שימוש בכלי bcp לייבוא וייצוא של נתונים
שימוש בהוספה בכמות גדולה לייבוא נתונים
שימוש ב-SqlPackage לייבוא וייצוא של נתונים
שימוש בייבוא וייצוא עם פסים
אימות מסד הנתונים המיובא

לא להשתמש בקטגוריות של Cloud Storage עם התכונה 'מגיש הבקשה משלם'

אי אפשר להשתמש בקטגוריה של Cloud Storage שבה מופעלת התכונה מגיש הבקשה משלם לצורך ייבוא וייצוא מ-Cloud SQL.

דחיסת נתונים כדי להפחית את העלות

‫Cloud SQL תומך בייבוא ובייצוא של קבצים דחוסים וגם של קבצים לא דחוסים. דחיסה יכולה לחסוך נפח אחסון משמעותי ב-Cloud Storage ולהפחית את עלויות האחסון, במיוחד כשמייצאים מופעים גדולים.

כשמייצאים קובץ BAK, משתמשים בסיומת הקובץ .gz כדי לדחוס את הנתונים. כשמייבאים קובץ עם סיומת של .gz, הוא נפרס באופן אוטומטי.

צמצום תהליכי ייבוא וייצוא ארוכים

ייבוא ל-Cloud SQL וייצוא מ-Cloud SQL יכולים להימשך זמן רב, בהתאם לגודל הנתונים שעוברים עיבוד. ההשלכות האפשריות הן:

אי אפשר לעצור פעולה של מופע Cloud SQL שפועלת במשך זמן רב.
אפשר לבצע רק פעולת ייבוא או ייצוא אחת בכל פעם לכל מופע, וייבוא או ייצוא שפועלים לאורך זמן חוסמים פעולות אחרות, כמו גיבויים יומיים אוטומטיים.

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

בדרך כלל, כשמבצעים העברה של מסד נתונים שלם, מומלץ להשתמש בקובצי BAK ולא בקובצי SQL לייבוא. בדרך כלל, ייבוא מקובץ SQL נמשך הרבה יותר זמן מייבוא מקובץ BAK.

שימוש ב-SqlPackage לייבוא וייצוא של נתונים

אפשר לייבא ולייצא נתונים ב-Cloud SQL באמצעות SqlPackage. הוא מאפשר לכם לייצא מסד נתונים של SQL, כולל סכימת מסד הנתונים ונתוני המשתמש, לקובץ BACPAC ‏ (.bacpac) ולייבא את הסכימה ואת נתוני הטבלה מקובץ BACPAC למסד נתונים חדש של משתמשים.

הכלי SqlPackage משתמש בפרטי הכניסה שלכם כדי להתחבר ל-SQL Server ולבצע ייבוא וייצוא של מסדי נתונים. הוא מאפשר העברות לכל המשתמשים ב-Cloud SQL. כדי לבצע פעולות ייבוא וייצוא, צריך:

תחנת עבודה שמחוברת למופע, שבה אפשר להריץ את SqlPackage. מידע נוסף על אפשרויות קישוריות
‫SqlPackage מותקן במערכת. מידע נוסף על הורדה והתקנה של SqlPackage זמין במסמכי התיעוד של מיקרוסופט.
פרטי הכניסה מוגדרים לגישה למופע. מידע נוסף על הגדרת פרטי כניסה זמין במאמר איך מאמתים ב-Cloud SQL.

דוגמאות

ייבוא

כדי לייבא נתונים למסד נתונים AdventureWorks2017, מריצים את הפקודה הבאה:

c:\Program Files\Microsoft SQL Server\160\DAC\bin>SqlPackage
/Action:Import /tsn:myTargetServer /tdn:AdventureWorks2017
/tu:myUsername /sf:mySourceFile
/TargetTrustServerCertificate:True /tp:myPassword

כאן,

‫mySourceFile הוא קובץ מקור שרוצים להשתמש בו כמקור לפעולה מאחסון מקומי. אם משתמשים בפרמטר הזה, אף פרמטר אחר של מקור לא תקף.
‫myTargetServer הוא שם השרת שמארח את מסד הנתונים של היעד.
‫myUsername הוא שם המשתמש ב-SQL Server שבו רוצים להשתמש כדי לגשת למסד הנתונים של היעד.
‫myPassword היא הסיסמה בפרטי הכניסה.

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

ייצוא

כדי לייצא נתונים ממסד נתונים AdventureWorks2017, מריצים את הפקודה הבאה:

c:\Program Files\Microsoft SQL Server\160\DAC\bin>SqlPackage
/Action:Export /TargetFile:"myTargetFile"
/ssn:mySourceServer /su:myUsername /sdn:AdventureWorks2017
/SourceTrustServerCertificate:True /sp:myPassword

כאן,

‫myTargetFile הוא קובץ היעד (קובץ ‎ .dacpac) שרוצים להשתמש בו כיעד לפעולה במקום במסד נתונים. אם משתמשים בפרמטר הזה, אי אפשר להשתמש בפרמטרים אחרים של יעד. הפרמטר הזה לא תקין לפעולות שתומכות רק ביעדי מסד נתונים.
‫myUsername הוא שם המשתמש ב-SQL Server שבו רוצים להשתמש כדי לגשת למסד הנתונים של המקור.
‫mySourceServer הוא שם השרת שמארח את מסד הנתונים של המקור.
‫myPassword היא הסיסמה בפרטי הכניסה.

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

שימוש בכלי bcp לייבוא וייצוא של נתונים

אפשרות נוספת לייבוא וייצוא של נתונים ב-Cloud SQL היא באמצעות כלי השירות של תוכנית העתקה בכמות גדולה (bcp). באמצעות כלי השירות bcp, אפשר לייצא נתונים ממסד נתונים של SQL Server לקובץ נתונים, ולייבא נתונים מקובץ נתונים למסד נתונים של SQL Server. כלי השירות bcp משתמש בפרטי הכניסה שלכם כדי להתחבר ל-SQL Server ולבצע ייבוא וייצוא של מסדי נתונים. היא מאפשרת לכל משתמשי Cloud SQL לבצע העברות. כדי לבצע פעולות ייבוא וייצוא, צריך:

תחנת עבודה שבה אפשר להריץ את כלי ה-bcp, ושמחוברת למופע Cloud SQL. מידע נוסף על אפשרויות קישוריות
כלי bcp שמותקן במערכת. מידע נוסף על הורדה והתקנה של bcp זמין במסמכי התיעוד של Microsoft.
פרטי הכניסה מוגדרים לגישה למופע. מידע נוסף על הגדרת פרטי כניסה זמין במאמר איך מאמתים ב-Cloud SQL.

דוגמאות

ייבוא

כדי לייבא נתונים מקובץ person.csv לטבלה Person במסד הנתונים AdventureWorks2017, מריצים את הפקודה הבאה:

bcp Person.Person in "person.csv" -d AdventureWorks2017 -U myLoginID -S myServer

כאן,

‫myLoginID הוא מזהה הכניסה שמשמש לחיבור ל-SQL Server.
‫myServer הוא המופע של SQL Server שאליו רוצים להתחבר. אם לא מציינים שרת, כלי bcp מתחבר למופע ברירת המחדל של SQL Server במחשב המקומי.

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

ייצוא

כדי לייצא נתונים מהטבלה Person במסד הנתונים AdventureWorks2017 לקובץ person.dat, מריצים את הפקודה הבאה:

bcp Person.Person out "person.dat" -U myLoginID -S myServer -d AdventureWorks2017

כאן,

‫myLoginID הוא מזהה הכניסה שמשמש לחיבור ל-SQL Server.
‫myServer הוא המופע של SQL Server שאליו רוצים להתחבר. אם לא מציינים שרת, כלי bcp מתחבר למופע ברירת המחדל של SQL Server במחשב המקומי.

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

שימוש בהוספה בכמות גדולה לייבוא נתונים

הוספה בכמות גדולה מאפשרת לייבא נתונים למסד הנתונים של Cloud SQL ל-SQL Server מקובץ שמאוחסן ב-Cloud Storage.

בקטע הזה מתוארים הנושאים הבאים:

תפקידים והרשאות נדרשים

כדי להגדיר הוספה בכמות גדולה, צריך:

ההרשאה CONTROL במסד הנתונים שאליו רוצים לייבא את הנתונים.
מפתח גישה מסוג HMAC וסוד שמופה לחשבון IAM עם ההרשאות הבאות:
- storage.buckets.get
- ‫storage.objects.create ו-storage.multipartUploads.create לכתיבת יומני שגיאות ודוגמאות לנתונים לא טובים.
אפשר גם להשתמש בתפקידים הבאים:
- Storage Object Viewer
- Storage Object Creator למידע על כתיבת יומני שגיאות ודוגמאות לנתונים לא טובים.

כדי להשתמש בהוספה בכמות גדולה, צריך:

הרשאת EXECUTE בתהליך המאוחסן msdb.dbo.gcloudsql_bulk_insert. מערכת Cloud SQL יוצרת את הפרוצדורה המאוחסנת אחרי שמפעילים את ההוספה בכמות גדולה במכונה. ב-Cloud SQL, ההרשאה EXECUTE מוענקת לחשבון האדמין sqlserver כברירת מחדל.
ההרשאה INSERT באובייקט שאליו רוצים לייבא את הנתונים.

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

שיקולים כשמשתמשים בהוספה בכמות גדולה

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

אבטחה

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

הפעולות הבאות עלולות להעביר בטעות את מפתח הגישה ואת הסוד ולהפוך אותם לזמינים:

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

מומלץ להסיר את המפתח ואת הסוד ממופע היעד אחרי שמבצעים את הפעולות האלה.

הוספה בכמות גדולה יכולה לכתוב נתונים שהיא לא יכולה לנתח לקובץ שמאוחסן בקטגוריה של Cloud Storage. אם רוצים להגן על נתונים שיש לטעינה בכמות גדולה גישה אליהם, צריך להגדיר VPC Service Controls.

ביצועים

כדי לצמצם את ההשפעה על הביצועים בזמן שימוש בהוספה בכמות גדולה, מומלץ לבצע את הפעולות הבאות:

צריך לבדוק ולהגדיר ערך מתאים ל-@batchsize, כי כברירת מחדל כל הנתונים מיובאים באצווה אחת.
אם מדובר בהוספות גדולות, כדאי להשבית את האינדקסים באופן זמני כדי להאיץ את הוספת הנתונים.
אם אפשר, כדאי להשתמש באפשרות @tablock כי היא יכולה לצמצם את התחרות על משאבים ולשפר את ביצועי טעינת הנתונים.
משתמשים בפרמטר @ordercolumnsjson כדי לציין נתונים ממוינים לפי סדר האינדקס המקובץ. כך אפשר לשפר את הביצועים של המופע.

אמינות

כדי לצמצם את ההשפעה על מהימנות המופע בזמן שימוש בהוספה בכמות גדולה, מומלץ לבצע את הפעולות הבאות:

אם מתרחשת שגיאה והמערכת משתמשת ב-@batchsize, יכול להיות שהנתונים ייטענו באופן חלקי. יכול להיות שתצטרכו לנקות את הנתונים האלה באופן ידני במופע שלכם.
אפשר להשתמש באפשרות @errorfile כדי לשמור יומן של שגיאות ודוגמאות לנתונים לא תקינים שזוהו במהלך תהליך הטעינה. כך קל יותר לזהות שורות שלא נטענו.

ביצוע הוספה בכמות גדולה

אפשר לבצע את פעולת ההוספה בכמות גדולה באמצעות הפרוצדורה המאוחסנת הבאה:

msdb.dbo.gcloudsql_bulk_insert

מידע נוסף זמין במאמר בנושא פרוצדורה מאוחסנת לשימוש בהוספה בכמות גדולה.

דוגמה: ייבוא נתונים מקובץ ב-Cloud Storage וציון קובץ שגיאות

1. הפעלת הוספה בכמות גדולה

כדי להפעיל הוספה בכמות גדולה במופע, מפעילים את הדגל cloud sql enable bulk insert.

gcloud sql instances patch INSTANCE_NAME --database-flags="cloud sql enable bulk insert"=on

מחליפים את INSTANCE_NAME בשם המכונה שרוצים להשתמש בה להוספה בכמות גדולה.

מידע נוסף זמין במאמר בנושא הגדרת דגלים של מסד נתונים.

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

2. יצירת מפתח HMAC

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

3. יצירת נתונים לדוגמה לייבוא

בעזרת כלי לעריכת טקסט, יוצרים קובץ עם קידוד ANSI או UTF-16 שמכיל את נתוני הדוגמה הבאים. שומרים את הקובץ בקטגוריה של Cloud Storage ונותנים לו שם, למשל bulkinsert.bcp.
```
1,Elijah,Johnson,1962-03-21
2,Anya,Smith,1982-01-15
3,Daniel,Jones,1990-05-21
```

יוצרים קובץ פורמט באמצעות הנתונים לדוגמה הבאים. שומרים את הקובץ בקטגוריה של Cloud Storage ונותנים לו שם, למשל bulkinsert.fmt. מידע נוסף על קבצים בפורמט XML ופורמטים אחרים ב-SQL Server זמין במאמר בנושא יצירת קובץ פורמט.

13.0
4
1       SQLCHAR             0       7       ","      1     PersonID               ""
2       SQLCHAR             0       25      ","      2     FirstName            SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       30      ","      3     LastName            SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       11      "\r\n"   4     BirthDate             ""

4. הפעלת התהליך המאוחסן

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

USE MASTER
GO
-- create test database
DROP DATABASE IF EXISTS bulktest
CREATE DATABASE bulktest
GO

-- create table to insert
USE bulktest;
GO
CREATE TABLE dbo.myfirstimport(
PersonID smallint,
FirstName varchar(25),
LastName varchar(30),
BirthDate Date
);

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

  -- create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';

-- create database scoped credential
CREATE DATABASE SCOPED CREDENTIAL GCSCredential
WITH IDENTITY = 'S3 Access Key',
SECRET = '<Access key>:<Secret>';

--create external data source
CREATE EXTERNAL DATA SOURCE GCSStorage
WITH ( TYPE = BLOB_STORAGE,
LOCATION = 's3://storage.googleapis.com/bulk-insert-demo/'
, CREDENTIAL = GCSCredential
);

CREATE EXTERNAL DATA SOURCE GCSStorageError
WITH ( TYPE = BLOB_STORAGE,
LOCATION = 's3://storage.googleapis.com/bulk-insert-demo/'
, CREDENTIAL = GCSCredential
);

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

EXEC msdb.dbo.gcloudsql_bulk_insert
@database = 'bulktest',
@schema = 'dbo',
@object = 'myfirstimport',
@file = 's3://storage.googleapis.com/bulk-insert-demo/bulkinsert.bcp',
@formatfile = 's3://storage.googleapis.com/bulk-insert-demo/bulkinsert.fmt',
@fieldquote = '"',
@formatfiledatasource = 'GCSStorage',
@ROWTERMINATOR = '0x0A',
@fieldterminator = ',',
@datasource ='GCSStorage',
@errorfiledatasource = 'GCSStorageError',
@errorfile = 's3://storage.googleapis.com/oom-data/bulkinsert/bulkinsert_sampleimport.log',
@ordercolumnsjson =
'[{"name": "PersonID","order": " asc "},{"name": "BirthDate","order": "asc"}]'

צפייה בנתונים המיובאים

אפשר לראות את הנתונים המיובאים באחת מהשיטות הבאות:

מריצים את השאילתה הבאה:
```
SELECT * FROM dbo.myfirstimport
```
‫Cloud SQL מוסיף רשומה של התהליך הזה ליומן השגיאות של SQL. אפשר לראות את זה ב-Cloud Logging. אפשר גם לראות את זה בנתוני יומן השגיאות של SQL ב-SQL Server Management Studio (SSMS).

השבתת הוספה בכמות גדולה

כדי להשבית את ההוספה בכמות גדולה, מסירים את הדגל cloud sql enable bulk insert:

  gcloud sql instances patch INSTANCE_NAME --database-flags="cloudsql enable bulk insert"=off

מחליפים את INSTANCE_NAME בשם המכונה שרוצים להסיר ממנה את ההוספה בכמות גדולה.

לחלופין, אפשר להריץ את הפקודה הבאה כדי לנקות את כל הדגלים של מסד הנתונים:

  gcloud sql instances patch INSTANCE_NAME --clear-database-flags

מחליפים את INSTANCE_NAME בשם המכונה שרוצים להסיר ממנה את ההוספה בכמות גדולה.

שימוש בייבוא וייצוא עם פסים

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

אימות מסד הנתונים המיובא

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

מגבלות ידועות

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

סיכוני אבטחה

ייבוא של קובץ גיבוי של SQL Server ‏ (.bak) ממקור לא מהימן עלול להוביל לסיכוני אבטחה חמורים.

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

קובץ גיבוי זדוני (.bak) עלול לחשוף את האפליקציות שלכם לסיכוני האבטחה הבאים:

קובץ הגיבוי המיובא יכול לתפוס חלק גדול ממופע SQL Server.
קובץ הגיבוי המיובא יכול להסלים הרשאות ולגשת למידע ללא אישור.

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

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