Go to Qiita Advent Calendar Top
LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@taka_yayoi(Takaaki Yayoi)inデータブリックス・ジャパン株式会社

DatabricksのCOPY INTOコマンド

Last updated at Posted at 2022-06-24

COPY INTO | Databricks on AWS [2022/6/9時点]の翻訳です。

本書は抄訳であり内容の正確性を保証するものではありません。正確な内容に関しては原文を参照ください。

ファイルの格納場所からDeltaテーブルにデータをロードします。これはリトライ可能かつ冪等性のあるオペレーションであり、ソース格納場所にあるすでにロードされたファイルはスキップされます。サンプルは、COPY INTOによる一般的なロードのパターンをご覧ください。

構文

SQL
COPY INTO target_table
  FROM { source |
         ( SELECT expression_list FROM source ) }
           [ WITH (
             [ CREDENTIAL { credential_name |
               (temporary_credential_options) } ]
             [ ENCRYPTION (encryption_options) ])
           ]
  FILEFORMAT = data_source
  [ VALIDATE [ ALL | num_rows ROWS ] ]
  [ FILES = ( file_name [, ...] ) | PATTERN = regex_pattern ]
  [ FORMAT_OPTIONS ( { data_source_reader_option = value } [, ...] ) ]
  [ COPY_OPTIONS ( { copy_option = value } [, ...] ) ]

パラメーター

  • target_table
    既存のDeltaテーブルを識別します。target_tableに時期の指定を含めることはできません。

    delta.`/path/to/table`のように場所を指定する形でテーブル名が指定された場合、Unity Catalogは書き込まれる場所に対するアクセスを統制することができます。外部のロケーションには以下の方法で書き込みを行うことができます。

    • 外部のロケーションとして場所を定義し、その外部ロケーションに対するWRITE FILES権限を定義する。
    • COPY INTO delta.`/some/location` WITH (CREDENTIAL <named_credential>)を用いて、認証情報を提供する名前付きストレージ認証情報に対するWRITE FILESを持っている。
      詳細はManage external locations and storage credentialsを参照ください。

    プレビュー
    Unity Catalogはパブリックプレビューです。プレビューに参加したい場合にはDatabricks担当に連絡してください。

  • source
    データを読み込むファイルの場所です。この場所にあるファイルはFILEFORMATで指定されるフォーマットである必要があります。場所の情報はURIの形式で指定されます。

    ソースの場所へのアクセスは以下の方法で指定されます。

    • credential_name
      ストレージの格納場所に書き込む、アクセスするために使用する認証情報の名称です。これはオプションです。ファイルが外部格納場所に含まれていない場合にのみこの認証情報を使用します。
    • クラスターのインスタンスプロファイル
    • インラインの一時的な認証情報。
    • Unity Catalogを通じて外部格納場所としてソース格納場所を定義し、外部格納場所に対するREAD FILESを設定する。
    • Unity Catalogを通じて、読み込みをおこな場所に対する認証情報を提供するREAD FILES権限を持つ名前付きストレージ認証情報を使用する。

    プレビュー
    Unity Catalogはパブリックプレビューです。プレビューに参加したい場合にはDatabricks担当に連絡してください。

    使用するためのk源を持っている外部ロケーションとしてパスがすでに設定されている場合には、インラインや名前付き認証情報を使用する必要はありません。詳細は、Manage external locations and storage credentialsをごんらんください。

    注意
    ソースのファイルパスがルートパスの場合、末尾にスラッシュ(/)を追加してください。例えば、s3://my-bucket/のようになります。

    受け付ける認証情報のオプションは以下の通りです。

    • AWS S3ではAWS_ACCESS_KEY, AWS_SECRET_KEY, AWS_SESSION_TOKEN
    • ADLS Gen2とAzure Blob StorageではAZURE_SAS_TOKEN

    受け付ける暗号化オプションは以下の通りでうs。

    • AWS S3ではTYPE = 'AWS_SSE_C'とMASTER_KEY

    Use temporary credentials to load data with COPY INTOをご覧ください。

  • SELECT expression_list

    Deltaテーブルにコピーする前にソースデータから特定のカラムあるいは表現式を選択します。表現式はウィンドウオペレーションを含むSELECT分で使用できる任意のものとなります。グローバルの集計処理でのみ集計表現を使用することができ、この構文ではカラムに対するGROUP BYを使用することはできません。

  • FILEFORMAT = data_source

    ロードするソースファイルのフォーマットです。CSV, JSON, AVRO, ORC, PARQUET, TEXT, BINARYFILEのいずれかとなります。

  • VALIDATE

    テーブルにロードされるデータが検証されますが、テーブルには書き込まれません。これらの検証には以下のものが含まれます。

    • データをパースできるかどうか。
    • テーブルのスキーマにマッチするかどうか、あるいはスキーマが進化する必要があるかどうか。
    • 全てのNULL可能性、チェック制約が合致するかどうか。

    デフォルトでは、ロードするデータの全てが検証されます。VALIDATE 15 ROWSのように検証する行数をROWSキーワードで指定することができます。COPY INTO文は50行のデータプレビュー、ROWSキーワードで50以下の値が指定された場合にはそれより少ない数のデータプレビューが表示されます。

    注意
    VALIDATEモードはDatabricksランタイム10.3以降で使用できます。

  • FILES

    ロードするファイル名のリストであり最大数は1000です。PATTERNと一緒に使用することはできません。

  • PATTERN

    ソースディレクトリからロードするファイルを特定する正規表現パターンです。FILESと一緒に指定することはできません。

  • FORMAT_OPTIONS

    Apache Sparkデータソースリーダーに渡される指定されたフォーマットに対応するオプションです。それぞれのファイルフォーマットのフォーマットオプションをご覧ください。

  • COPY_OPTIONS

    COPY INTOコマンドのオペレーションを制御するオプションです。

    • force: ブール値、デフォルトはfalseです。trueに設定すると、冪等性は無効化され、以前ロードしたかどうかに関係なくファイルがロードされます。
    • mergeSchema: ブール値、デフォルトはfalseです。trueに設定するとスキーマは入力データに応じて進化します。テーブルのスキーマを進化させるには、テーブルに対するOWN権限を持っている必要があります。

    注意
    mergeSchemaオプションはDatabricksランタイム10.3以降で使用できます。

ファイルメタデータへのアクセス

ファイルベースデータソースのメタデータにアクセスする方法に関しては、Databricksにおけるファイルメタデータカラムをご覧ください。

フォーマットオプション

一般的なオプション

以下のオプションはすべてのファイルフォーマットに適用されます。

オプション 型 説明 デフォルト値
modifiedAfter タイムスタンプの文字列, 例えば2021-01-01 00:00:00.000000 UTC+0 指定されたタイムスタンプ以降の変更タイムスタンプをもつファイルを取り込むオプションのタイムスタンプ None
modifiedBefore タイムスタンプの文字列, 例えば2021-01-01 00:00:00.000000 UTC+0 指定されたタイムスタンプ以前の変更タイムスタンプをもつファイルを取り込むオプションのタイムスタンプ None
pathGlobFilter 文字列 ファイルを選択するために指定するglobパターン。COPY INTOのPATTERNと同じです。 None
recursiveFileLookup ブール値 ベースディレクトリ内のデータを再帰的にロードし、パーティション推定をスキップするかどうか。 false

JSONオプション

オプション
allowBackslashEscapingAnyCharacter
タイプ: Boolean
直後の文字をエスケープするためにバックスラッシュを許可するかどうかを指定します。無効化されている場合、JSON仕様で明示的に一覧されている文字のみがエスケープされます。
デフォルト値: false
allowComments
タイプ: Boolean
パースされたコンテンツ内でJava, C, C++スタイルのコメント('/', '*', '//')を許可するかどうかを指定します。
デフォルト値: false
allowNonNumericNumbers
タイプ: Boolean
not-a-number(NaN)を適切な浮動小数点の値として許可するかどうかを指定します。
デフォルト値: true
allowNumericLeadingZeros
タイプ: Boolean
ゼロで始まる整数値(00001など)を許可するかどうかを指定します。
デフォルト値: false
allowSingleQuotes
タイプ: Boolean
文字列を引用(名前や文字列の値)するためにシングルクオート(アポストロフィ、'\')を許可するかどうかを指定します。
デフォルト値: true
allowUnquotedControlChars
タイプ: Boolean
JSON文字列の中にエスケープされていない制御文字(32より小さい値のASCII文字、タブ、ラインフィード文字を含む)を含めて良いかどうかを指定します。
デフォルト値: false
allowUnquotedFieldNames
タイプ: Boolean
クオートされていないフィールド名(JavaScriptでは許可されますが、JSON仕様では許可されません)の利用を許可するかどうかを指定します。
デフォルト値: false
badRecordsPath
タイプ: String
不正なJSONレコードに関する情報を記録するファイルのパスです。
デフォルト値: None
columnNameOfCorruptRecord
タイプ: String
不正でパースできないレコードを格納するカラム名です。パーシングのmodeがDROPMALFORMEDの場合、このカラムは空になります。
デフォルト値: _corrupt_record
dateFormat
タイプ: String
日付文字列をパースするフォーマットです。
デフォルト値: yyyy-MM-dd
dropFieldIfAllNull
タイプ: Boolean
スキーマ推定の際に、全てがnull値のカラム、空の配列、空のstructsのカラムを無視するかどうかを指定します。
デフォルト値: false
encodingあるいはcharset
タイプ: String
JSONファイルのエンコーディング名を指定します。オプションについてはjava.nio.charset.Charsetをご覧ください。multilineがtrueの場合、UTF-16やUTF-32を使うことはできません。
デフォルト値: UTF-8
ignoreCorruptFile
タイプ: Boolean
破損したファイルを無視するかどうか。trueの場合、破損したファイルに遭遇してもSparkジョブは処理を続け、読み込んだファイルを取得することができます。Delta Lake履歴のoperationMetricsカラムのnumSkippedCorruptFilesで確認することができます。Databricks 11.0以降で利用できます。
デフォルト値: false
ignoreMissingFiles
タイプ: Boolean
存在しないファイルを無視するかどうか。trueの場合、存在しないファイルに遭遇してもSparkジョブは処理を続け、読み込んだファイルを取得することができます。Databricks 11.0以降で利用できます。
デフォルト値: false (COPY INTOではtrue)
inferTimestamp
タイプ: Boolean
タイムスタンプ文字列をTimestampTypeとして推定するかどうかを指定します。trueに設定すると、スキーマ推定の処理時間が長くなります。
デフォルト値: false
lineSep
タイプ: String
二つの連続するJSONレコードの区切り文字です。
デフォルト値: None、\r, \r\n, \nをカバーします。
lineSep
タイプ: String
連続する2つのJSONレコードの区切り文字です。
デフォルト値: None、\r, \r\n, \nから選択できます。
locale
タイプ: String
java.util.LocaleのIDです。JSONのパーシングする際に、デフォルトの日付、タイムスタンプ、数値に影響を与えます。
デフォルト値: US
mode
タイプ: String
不正レコードのハンドリングにおけるパーサーのモードです。'PERMISSIVE', 'DROPMALFORMED', 'FAILFAST'のいずれかとなります。
デフォルト値: PERMISSIVE
multiLine
タイプ: Boolean
JSONレコードが複数行にわたるかどうかを指定します。
デフォルト値: false
prefersDecimal
タイプ: Boolean
スキーマ推定の際、floatやdoubleをDecimalTypeとして推定するかどうかを指定します。
デフォルト値: false
primitivesAsString
タイプ: Boolean
数字やbooleanのようなプリミティブ型をStringTypeとして推定するかどうかを指定します。
デフォルト値: false
rescuedDataColumn
タイプ: String
データ型の不一致やスキーマミスマッチ(カラム名の大文字小文字を含む)でパーシングできなかった全てのデータを別のカラムで収集するかどうかを指定します。Auto Loaderを使用する際には、このカラムは自動で含まれます。詳細はRescued data columnを参照ください。
デフォルト値: None
timestampFormat
タイプ: String
パースするタイムスタンプ文字列のフォーマットです。
デフォルト値: yyyy-MM-dd'T'HH:mm:ss[.SSS][XXX]
timeZone
タイプ: String
タイムスタンプと日付をパーシングする際に使用するjava.time.ZoneIdとなります。
デフォルト値: None

CSVオプション

オプション
badRecordsPath
タイプ: String
不正なCSVレコードに関する情報を記録するファイルのパスです。
デフォルト値: None
charToEscapeQuoteEscaping
タイプ: Char
クオートをエスケープするために使用する文字です。例えばレコード[ " a\\", b ]に対して
  • '\'をエスケープする文字が定義されていない場合、レコードはパースされません。パーサーは文字列[a],[\],["],[,],[ ],[b]を読み込み、クローズするクオートがないためエラーを発生させます。
  • '\'をエスケープする文字が\として定義されている場合、レコードが読み込まれ[a\]と[b]の2つの値が読み込まれます。

デフォルト値: '\0'
columnNameOfCorruptRecord
タイプ: String
不正でパースできないレコードを格納するカラム名です。パーシングのmodeがDROPMALFORMEDの場合、このカラムは空になります。
デフォルト値: _corrupt_record
comment
タイプ: Char
テキストの行の最初にある場合に行コメントと表現する文字を定義します。コメントのスキップを無効化するには'\0'を使用します。
デフォルト値: '#'
dateFormat
タイプ: String
日付文字列をパースするフォーマットです。
デフォルト値: yyyy-MM-dd
emptyValue
タイプ: String
空の値を表現する文字列です。
デフォルト値: ""
encodingあるいはcharset
タイプ: String
CSVファイルのエンコーディング名を指定します。オプションについてはjava.nio.charset.Charsetをご覧ください。multilineがtrueの場合、UTF-16やUTF-32を使うことはできません。
デフォルト値: UTF-8
enforceSchema
タイプ: Boolean
CSVファイルに対して、指定された、あるいは推定されたスキーマの適用を強制するかどうかを指定します。オプションが有効化された場合、CSVファイルのヘッダーは無視されます。Auto Loaderを使用する際、データを救助しスキーマ進化を実現するために、デフォルトでこのオプションは無視されます。
デフォルト値: true
escape
タイプ: Char
データをパーシングする際に使用するエスケープ文字です。
デフォルト値: '\'
header
タイプ: Boolean
CSVファイルにヘッダーがあるかどうかを指定します。スキーマを推定する際、Auto Loaderはファイルにヘッダーがあることを前提にします。
デフォルト値: false
ignoreLeadingWhiteSpace
タイプ: Boolean
パースされた値それぞれの頭の空白を無視するかどうかを指定します。
デフォルト値: false
ignoreTrailingWhiteSpace
タイプ: Boolean
パースされた値それぞれの後ろの空白を無視するかどうかを指定します。
デフォルト値: false
inferSchema
タイプ: Boolean
パースされたCSVレコードのデータ型を推定するか、全てのカラムがStringTypeであると仮定するかどうかを指定します。trueに設定すると、データに対する追加の処理が必要となります。
デフォルト値: false
lineSep
タイプ: String
二つの連続するCSVレコードの区切り文字です。
デフォルト値: None、\r, \r\n, \nをカバーします。
locale
タイプ: String
java.util.LocaleのIDです。JSONのパーシングする際に、デフォルトの日付、タイムスタンプ、数値に影響を与えます。
デフォルト値: US
maxCharsPerColumn
タイプ: Int
パースする値の最大文字数です。メモリーエラーの回避に使うことができます。デフォルトは-1であり、無制限となります。
デフォルト値: -1
maxColumns
タイプ: Int
レコードあたりの最大列数のハードリミットです。
デフォルト値: 20480
mergeSchema
タイプ: Boolean
複数ファイルのスキーマを推定し、ファイルごとのスキーマをマージするかどうかを指定します。Auto Loaderでスキーマ推定を行う際にはデフォルトで有効化されます。
デフォルト値: false
mode
タイプ: String
不正レコードのハンドリングにおけるパーサーのモードです。'PERMISSIVE', 'DROPMALFORMED', 'FAILFAST'のいずれかとなります。
デフォルト値: PERMISSIVE
multiLine
タイプ: Boolean
CSVレコードが複数行にわたるかどうかを指定します。
デフォルト値: false
nanValue
タイプ: String
FloatTypeやDoubleTypeをパーシングする際のnon-a-numberの値の文字列表現を指定します。
デフォルト値: "NaN"
negativeInf
タイプ: String
FloatTypeやDoubleTypeをパーシングする際の負の無限大の文字列表現を指定します。
デフォルト値: "-Inf"
nullValue
タイプ: String
ヌル値を示す文字列です。
デフォルト値: ""
parserCaseSensitive(非推奨)
タイプ: Boolean
ファイルを読み込んでいる間にヘッダーで宣言されているカラムをスキーマに割り当てる際に、大文字小文字を区別するかどうかを指定します。有効化されている場合、大文字小文字が異なるカラムはrescuedDataColumnにレスキューされます。このオプションはreaderCaseSensitiveによって非推奨となりました。
デフォルト値: false
positiveInf
タイプ: String
FloatTypeやDoubleTypeをパーシングする際の正の無限大の文字列表現を指定します。
デフォルト値: "Inf"
quote
タイプ: Char
値にフィールドの区切り文字が含まれている際に使用されるエスケープ文字です。
デフォルト値: '\'
rescuedDataColumn
タイプ: String
データ型の不一致やスキーマミスマッチ(カラム名の大文字小文字を含む)でパーシングできなかった全てのデータを別のカラムで収集するかどうかを指定します。Auto Loaderを使用する際には、このカラムは自動で含まれます。詳細はRescued data columnを参照ください。
デフォルト値: None
sepあるいはdelimiter
タイプ: String
カラムの区切り文字です。
デフォルト値: ","
timestampFormat
タイプ: String
パースするタイムスタンプ文字列のフォーマットです。
デフォルト値: yyyy-MM-dd'T'HH:mm:ss[.SSS][XXX]
timeZone
タイプ: String
タイムスタンプと日付をパーシングする際に使用するjava.time.ZoneIdとなります。
デフォルト値: None
unescapedQuoteHandling
タイプ: String
エスケープされていないクオート(引用符)の取り扱い戦略を指定します。オプションは以下の通りとなります。
  • STOP_AT_CLOSING_QUOTE:　入力にエスケープされていないクオートが見つかった場合、クオートの文字列を蓄積し、クローズするクオートが見つかるまで値を引用された値としてパーシングを継続します。
  • BACK_TO_DELIMITER: 入力にエスケープされていないクオートが見つかった場合、値は引用されていないものとみなされます。これによって、sep で定義されている区切り文字が見つかるまで現状のパースされた値の全ての文字列が蓄積されます。値に区切り文字が含まれていない場合、パーサーは区切り文字か行の終わりを見つけるまで文字の蓄積を継続します。
  • STOP_AT_DELIMITER: 入力にエスケープされていないクオートが見つかった場合、値は引用されていないものとみなされます。これによって、sep で定義されている区切り文字か行の終わりが見つかるまで全ての文字列が蓄積されます。
  • SKIP_VALUE: 入力にエスケープされていないクオートが見つかった場合、当該の値でパスされたコンテンツは(次の区切り文字が見つかるまで)スキップされ、nullValue で設定された値になります。
  • RAISE_ERROR: 入力にエスケープされていないクオートが見つかった場合、TextParsingExceptionエラーを引き起こします。

デフォルト値: STOP_AT_DELIMITER

PARQUETオプション

オプション
datetimeRebaseMode
タイプ: String
JulianとProleptic Gregorianカレンダーの間のDATEとTIMESTAMPのリベースを制御します。EXCEPTION, LEGACY, CORRECTEDを指定できます。
デフォルト値: LEGACY
int96RebaseMode
タイプ: String
JulianとProleptic Gregorianカレンダーの間のINT96のリベースを制御します。EXCEPTION, LEGACY, CORRECTEDを指定できます。
デフォルト値: LEGACY
mergeSchema
タイプ: Boolean
複数のファイルのスキーマを推定し、ファイルごとのスキーマをマージするかどうかを指定します。
デフォルト値: false

AVROオプション

オプション
avroSchema
タイプ: String
ユーザーがAvroフォーマットで指定できるオプションのスキーマです。Avroを読み込んでいる際、このオプションに実際のAvroのスキーマと違うが互換性のある進化スキーマを設定することができます。
デフォルト値: None
datetimeRebaseMode
タイプ: String
JulianとProleptic Gregorianカレンダーの間のDATEとTIMESTAMPのリベースを制御します。EXCEPTION, LEGACY, CORRECTEDを指定できます。
デフォルト値: LEGACY
mergeSchema
タイプ: Boolean
複数のファイルのスキーマを推定し、ファイルごとのスキーマをマージするかどうかを指定します。
デフォルト値: false

BINARYFILEオプション

バイナリーファイルには追加の設定オプションはありません。

TEXTオプション

オプション
encoding
タイプ: String
CSVファイルのエンコーディング名を指定します。オプションについてはjava.nio.charset.Charsetをご覧ください。
デフォルト値: UTF-8
lineSep
タイプ: String
二つの連続するテキストレコードの区切り文字です。
デフォルト値: None、\r, \r\n, \nをカバーします。
wholeText
タイプ: Boolean
ファイルを単一のレコードとして読み込むかどうかを指定します。
デフォルト値: false

ORCオプション

オプション
mergeSchema
タイプ: Boolean
複数のファイルのスキーマを推定し、ファイルごとのスキーマをマージするかどうかを指定します。
デフォルト値: false

関連ドキュメント

Databricks 無料トライアル

Databricks 無料トライアル

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?