データ・フロー演算子の使用
データ統合では、データ・フロー演算子は、データ・フローで使用できる入力ソース、出力ターゲットおよび変換を表します。
「演算子」パネルから、演算子をキャンバスにドラッグして、データ・フローを設計します。次に、「プロパティ」パネルの「詳細」タブを使用して、各演算子の基本プロパティと必須プロパティを構成します。該当する場合は、「拡張オプション」タブを使用して他のプロパティを指定します。
一般に、データ・フロー演算子には、1つ以上のインバウンド・ポートと、データが流れる1つ以上のアウトバウンド・ポートを含めることができます。たとえば、フィルタ演算子、結合演算子およびターゲット演算子のインバウンド・ポートに、同じソース・アウトバウンド・ポートを接続できます。また、同じ結合インバウンド・ポートに別のソース演算子を接続することもできます。
データ・フローを有効にするには、データ・フローに少なくとも1つのソース演算子と1つのターゲット演算子が含まれている必要があります。データ統合では1つのデータ・フロー内で複数のターゲット演算子がサポートされますが、1つのターゲット演算子に含めることができるインバウンド・ポートは1つのみです。
演算子どうしを接続するには、一方の演算子に、その右側にコネクタ(小さい円)が表示されるまでカーソルを置きます。次に、そのコネクタを接続先の次の演算子にドラッグします。コネクタをドロップして、演算子どうしが線で結ばれると、その接続は有効になります。
接続線は、あるノードから別のノードへデータがどのように流れるかを示します。表示されているコネクタをあるオブジェクトから別のオブジェクトにドラッグできますが、1つのフィルタ、式、集計、個別、ソートおよびターゲット演算子に複数のインバウンド接続線を使用することはできません。
データ演算子
データ統合には、データ・フローの入力および変換済データの出力として機能する、入出力データ・エンティティを追加するためのソース演算子とターゲット演算子が用意されています。
データ・フロー内のデータ演算子の入力または出力データ・エンティティを構成するには、まずデータ・アセット、接続およびスキーマ(またはバケット)を選択します。
選択は、プロパティ・パネルの「詳細」タブに表示される順序で、リソース・タイプの横でオプションが有効になっているときに「選択」をクリックして行います。
たとえば、最初にソース演算子を追加すると、「データ・アセット」のみが選択可能になります。次のリソース・タイプ「接続」の選択オプションは、前のオブジェクトを選択した後にのみ有効になります。
リソースの横にある「選択」をクリックすると、目的のオブジェクトを選択するパネルが表示されます。リソース・メニューを使用してオブジェクトを選択することも、「すべて表示」をクリックし、別のパネルを使用して参照または検索し、オブジェクトを選択することもできます。
リソース・オブジェクトを選択すると、後続の各選択は、前の選択から継承された親子関係に基づきます。選択パネルの上部にあるブレッドクラムに、選択階層が表示されます。たとえば、データ・アセット「Oracle Databaseデータ・アセット1」と接続「デフォルト接続」を選択したとします。「スキーマ」を選択すると、ブレッドクラムに「接続のデフォルト接続を使用したOracle Databaseデータasset1から」と表示されます。
データ・アセット、接続およびスキーマ(またはバケット)を選択した後、使用可能なデータ・エンティティのリストからデータ・エンティティを選択します。
一般に、「データ・エンティティの参照」パネルでは次の操作を実行できます。
-
使用可能なデータ・エンティティを参照し、名前でエンティティを選択します。
-
検索する使用可能なリストをフィルタしてから選択します。検索フィールドで、エンティティ名の一部または全体を入力し、[Enter]を押して検索を開始します。検索は大/小文字を区別します。たとえば、使用可能なデータ・エンティティに
BANK_US
およびBANK_EU
が含まれる場合は、BANK
と入力し、フィルタされたリストから選択します。 -
検索文字列に1つ以上のパラメータを使用します。たとえば:
CUSTOMERS_${COUNTRY}
パラメータ化されたデータ・エンティティ名を使用して入力リソースまたは出力リソースを選択するには、データ・エンティティ名でのパラメータの使用を参照してください。
リソースのデータ・アセット・タイプによっては、データ・エンティティを選択した後、「プロパティ」パネルの「詳細」タブまたは「拡張オプション」タブで、より多くの構成が必要になる場合があります。
データ・アセットやデータ・フロー内の接続などのリソース・オブジェクトが特定のリソースに永続的にバインドされないようにするには、そのオブジェクトにパラメータを割り当てます。
パラメータの割当ては、リソース・オブジェクトの選択後または選択する前に行うことができます。
-
データ・フローで、ソースまたはターゲット演算子を追加します。リソース・タイプを選択した後、リソース名の横にある「パラメータの割当て」をクリックし、別のパネルを使用して、選択したオブジェクトのパラメータを選択して割り当てます。適切なパラメータ・タイプがない場合は、パラメータを追加して割り当てることができます。
ノート
「パラメータの割当て」は、名前にパラメータ(BANK_${REGION}
など)が含まれているデータ・エンティティでは使用できません。パラメータ化したエンティティ・リソースを別のパラメータにバインドすることはできません。 -
または、ソースまたはターゲット演算子を追加してから「パラメータの割当て」をクリックして、1つのパネルを使用してパラメータを割り当て、同時にオブジェクトのリソースを選択することもできます。「パラメータの追加」パネルで、前の選択から継承した親子関係に基づいてリソースを選択します。パネルの上部にあるブレッドクラムに、選択階層が表示されます。
データ・フロー・パラメータの使用も参照してください。
データ演算子に指定したデータ・エンティティ・リソースの名前に1つ以上のデータ・フロー・パラメータを含めることができます。
データ・エンティティ名のデータ・フロー・パラメータに使用する構文は、${PARAMETER_NAME}
です。たとえば: CUSTOMERS_${COUNTRY}
パラメータ名では大文字と小文字が区別され、各パラメータにはデフォルト値が必要です。
たとえば、CUSTOMERS_${COUNTRY}
がデータベース表CUSTOMERS_USA
を返し、BANK_${COUNTRY}/*
がBANK_EU
のオブジェクト・ストレージ・ファイルを返すことがあります。
データ演算子の構成時にデータ・エンティティ名にパラメータを使用するには、次の操作を実行できます。
- データ演算子を追加する前にデータ・フローにパラメータを追加します
- 演算子のデータ・エンティティの構成時にパラメータを追加します
パラメータの追加方法
データ・フローで、キャンバス・ツールバーの「表示」メニューから「パラメータ」を選択して「パラメータ」パネルを開きます。
「Parameters」パネルで、「Config」をクリックし、「Add」をクリックします。
「パラメータの追加」パネルで、適切なデータ型(VARCHARやNUMERICなど)のパラメータを構成し、デフォルト値を追加します。
データ・フローのデータ・エンティティの構成中に、「データ・エンティティの参照」パネルにパラメータ名を入力して、使用可能なデータ・エンティティを検索できます。検索フィールドに、${
に続けて任意の文字を入力します。パラメータ名の先頭部分がデータ・フロー内の既存パラメータと一致すると、候補名のリストが表示されます。リストからパラメータを選択するには、パラメータ名をクリックし、}
を追加して構文を完了してから、[Enter]を押します。
データ・エンティティ構成時にパラメータを追加する方法
「データ・エンティティの参照」パネルでは、次の操作を実行できます:
-
「More actions」メニューから、「Add data flow parameter」を選択して、「Add data flow parameter」パネルを使用します。追加して使用するパラメータのデータ型、デフォルト値およびその他のプロパティを指定します。
-
検索フィールドに、
${
に続けて任意の文字を入力します。パラメータ名の先頭部分がデータ・フロー内の既存パラメータと一致すると、候補名のリストが表示されます。リストからパラメータを選択するには、パラメータ名をクリックし、}
を追加して構文を完了してから、[Enter]を押します。 -
検索フィールドで、
${PARAMETER_NAME}
などのパラメータ名を入力します。そのパラメータ名がデータ・フローにまだ存在していない場合は、[Enter]を押すと、データ統合に「データ・フロー・パラメータの追加」パネルが表示されます。または、パラメータ名を入力した後、「その他のアクション」メニューから「データ・フロー・パラメータの追加」を選択します。「データ・フロー・パラメータの追加」パネルで、追加して使用するパラメータのデータ型、デフォルト値およびその他のプロパティを指定します。
ソース演算子
ソース演算子を使用して、データ・フローへの入力となるデータ・エンティティを指定します。
1つのデータ・フローに複数のソース演算子を追加できます。
ソース演算子に階層データ・エンティティを使用している場合は、階層データ型を参照して、何がサポートされているかを理解してください。「プロパティ」パネルの「属性」タブまたは「データ」タブでは、一部の構成を実行できない場合があります。
OCIオブジェクト・ストレージをデータ・ソースとして構成するときに、正規表現を使用して、1つ以上のデータ・エンティティを選択するためにファイル・パターンを指定できます。
ファイル・パターンは、ディレクトリやファイルの名前に一致するファイルを検索し、一致するファイルが見つかったらその処理方法を指定するためのルールです。
使用する構文
データ統合では、ファイル・パターンを指定するためのglobパターン構文をサポートしています。
- アスタリスク
*
は、任意の数の文字(なしも含む)に一致します。 - 2つのアスタリスク(
**
)は*
と同じように機能しますが、ディレクトリの境界を越えて完全なパスが照合します。 - 疑問符(
?
)は、1文字に一致します。 - 中カッコは、サブパターンの集まりを指定します。例:
{sun,moon,stars}
は、"sun"、"moon"または"stars"に一致します。{temp*,tmp*}
は、tempまたはtmpで始まるすべての文字列に一致します。
- 大カッコは、単一文字のセットを表します。ハイフン文字(
-
)が使用されている場合は、文字の範囲を表します。例:[aeiou]
は、任意の小文字の母音に一致します。[0-9]
は、任意の数字に一致します。[A-Z]
は、任意の大文字に一致します。[a-z,A-Z]
は、任意の大文字または小文字に一致します。
大カッコ内の
*
、?
および\
は、それ自体に一致します。 - それ以外の文字はすべて、それ自体に一致します。
*
、?
またはその他の特殊文字と一致させるには、バックスラッシュ文字\
を使用してその文字をエスケープします。例:\\
は単一のバックスラッシュに一致し、\?
は疑問符に一致します。
例
*.html | .html で終わるすべての文字列に一致します |
??? | 3つの文字または数字を含むすべての文字列に一致します |
*[0-9]* | 数値を含むすべての文字列に一致します |
*.{htm,html,pdf} | .htm 、.html または.pdf で終わる任意の文字列に一致します |
a?*.java | a で始まり、その後に1つ以上の文字または数字が続き、.java で終わる任意の文字列に一致します。 |
{foo*,*[0-9]*} | foo で始まる任意の文字列、または1つの数値を含む任意の文字列に一致します |
directory1/20200209/part-*[0-9]*json | ファイル名がpart- で始まり、任意の数の0-9 の数字を含み、json で終わる、フォルダ内のすべてのファイルに一致します |
directory3/**.csv | フォルダdirectory3 およびそのサブフォルダにある、拡張子がcsv のすべてのファイルに一致します |
directory3/*.csv | メイン・フォルダdirectory3 のみにある、拡張子がcsv のすべてのファイルに一致します。サブフォルダ内のファイルは含まれません。 |
式をテストして、使用するパターンによって1つ以上のデータ・エンティティのオブジェクト・ストレージ・ファイルが取得されることを確認できます。
-
「データ・エンティティの選択」パネルで、「パターンで参照」をクリックします。
-
「データ・エンティティをパターンで参照」パネルで、「その他のアクション」メニューから「パターンのテスト」を選択します。
-
「パターンのテスト」パネルの「検索パターン」フィールドにパターン式を入力し、使用する前にテストします。
たとえば、ディレクトリ
department1/2020
にある拡張子.json
のすべてのファイルを検索する場合は、department1/2020/*.json
と入力します。「検索パターン」フィールドでパラメータ構文${}
を使用できます。 -
検索パターンをテストするには、「テスト・ファイル名」ブロックに、1つのファイル名または改行文字で区切った複数のファイル名を指定します。たとえば、パターン
BANK_C*/*
の場合、ファイル名は次のようになります:BANK_CITY/part-00002-0aaf87d57fbc-c000.csv BANK_COUNTRY/part-00000-a66df3ab02fd-c000.csv
-
「パターンのテスト」をクリックします。
テスト・ファイル名が「結果ファイル名」ブロックに返されたことを確認します。
-
「パターンの使用」をクリックして、パターン式を「データ・エンティティをパターンで参照」パネルに追加します。
「データ・エンティティをパターンで参照」パネルに戻ります。表には、パターン式と一致するファイルが表示されます。
-
「パターンを選択」をクリックします。
「データ・エンティティの選択」パネルに戻ります。パターン式は「データ・エンティティ」の横に表示されます。
式を使用する場合、そのパターンに一致するすべての既存ファイルは同じ構造を持つものとみなされます。一致したファイルは、データ・フローで単一のエンティティとして扱われます。将来の新規ファイルがパターンと一致する場合も処理されます。
増分ロードでは、ソースからターゲットに新規または更新されたデータのみがロードされます。データ統合では、BICC Oracle Fusion Applicationsをソース・データとして構成するときに、管理対象増分抽出戦略を使用して増分ロードを実行できます。
増分抽出戦略の使用を選択すると、最終抽出日に基づいて、ソースから新規または更新されたレコードのみが抽出されます。データ統合には、2つの最終抽出日オプションがあります。
-
カスタム: タスク実行ごとに最終抽出日を指定します。
-
管理: データ統合は、タスク実行のタイムスタンプを追跡し、連続した実行で最後に成功したロード日をウォーターマークとして格納することで、日付を管理します。
データ統合管理の最終抽出日オプションでは、タスク実行の日付を明示的に指定する必要はありません。ただし、実行時に日付を上書きできます。
タスク・スケジュールによって開始されたタスク実行
データ統合は、タスク・スケジュールによって開始されたタスク実行とは関係なく、タスク実行を追跡します。そのため、「管理対象」の最終抽出日オプションを使用し、タスク・スケジュールも設定した場合、データ統合では、タスク・スケジュールによって開始されていないタスク実行の最終正常タスク実行タイムスタンプとは別に、タスク・スケジュール実行の最終正常タスク実行タイムスタンプが自動的に追跡されます。つまり、タスク・スケジュール内で管理される最終日またはタスク内で管理される最終日は、他の実行操作によって変更されません。
データ・フロー内の様々なデータ・エンティティの増分ロード
データ・フロー内の異なるデータ・エンティティに対して増分ロードを設定するとします。これを行うには、パラメータを使用して、データ・エンティティごとにタスク・スケジュールを作成します。一般的なステップは次のとおりです。
- データ・フローで、ソース・スキーマ(BICCオファリング)およびデータ・エンティティ(BICC VO)のパラメータを割り当てます。
- パラメータ化されたデータ・フローの統合タスクを作成および公開します。
- 統合タスクのタスク・スケジュールを作成します。「パラメータの構成」ページで、タスク・スケジュールのスキーマおよびデータ・エンティティ値を指定します。
- 同じ統合タスクに対して別のタスク・スケジュールを作成します。「パラメータの構成」ページで、このタスク・スケジュールのスキーマおよびデータ・エンティティ値を設定します。
1日に複数回増分ロード
増分ロードを1日に複数回実行するには、データ・フローのBICCソース演算子の直後にフィルタ演算子を追加します。次に、すでに処理されているデータをフィルタで除外する条件式を作成します。たとえば、BICC VOのlast_update_date列がLastUpdateDateField
の場合、式は次のようになります。
FILTER_1.MYSOURCE_1.LastUpdateDateField > ${SYS.LAST_LOAD_DATE}
ターゲット演算子
ターゲット演算子を使用して、変換されたデータを格納するための出力となるデータ・エンティティを指定します。
1つのデータ・フローに複数のターゲット演算子を追加できます。各ターゲットには、インバウンド・ポートを1つのみ含めることができます。
ターゲット演算子に階層データ・エンティティを使用している場合は、階層データ型を参照して、何がサポートされているかを理解してください。「プロパティ」パネルの「属性」タブ、「マップ」タブ、「データ」タブでは、一部の構成を実行できない場合があります。
「データ」タブには、データ・フローに適用された演算子に基づいて変換されたデータが表示されます。
ターゲット・エンティティのデータを名前パターンまたはデータ型でフィルタ処理できます。データを名前パターンでフィルタ処理するには、「パターンによるフィルタ」フィールドに単純な正規表現パターンまたはワイルドカード?および*を入力します。データを型でフィルタ処理するには、パターン・フィールドの横にあるメニューからデータ型を選択します。
データは読取り専用であるため、変換をターゲット演算子に適用できません。
「マップ」タブは、ターゲット演算子に対してのみ使用できます。
新しいターゲット・データ・エンティティを作成している場合、「マップ」タブは使用できません。受信属性は、1対1マッピングを使用した表またはファイル構造の作成に使用されます。
既存のターゲット・データ・エンティティを使用する場合は、受信属性をターゲット・データ・エンティティの属性にマップします。行うことができるアクションは:
受信属性を、リスト内の位置に従ってターゲット・エンティティ属性にマップします。
アクション・メニューから「位置による自動マップ」を選択します。「位置による自動マップ」ルールが追加されます。
受信属性を同じ名前のターゲット属性にマップします。
アクション・メニューから「名前による自動マップ」を選択します。「名前による自動マップ」ルールが追加されます。
ユーザー定義の単純な正規表現ルールに基づいて、受信属性をターゲット属性にマップします。
アクション・メニューから「パターンによるマップ」を選択します。ソース・パターンおよびターゲット・パターンを入力します。次に、「プレビュー」マッピングをクリックして、ソース・パターンとターゲット・パターンをテストします。
パターンを定義するには、アスタリスク(*)および疑問符(?)記号を使用できます。文字列パターン内の任意の文字数のワイルドカードを示すアスタリスクを使用します。疑問符を使用して、1文字のワイルドカードを示します。たとえば、*INPUT?
は、n個の文字から始まり、文字列INPUT
を含み、その後に単一の文字が続くすべての一致属性をマップします(NEWINPUTS
など)。
デフォルトでは、パターン照合では大文字/小文字を区別しません。たとえば、ソース・パターン*Name
は、ターゲット名CUSTOMER_NAME
およびCustomer_Name
に一致します。
異なる取得グループを指定するには、$n
を使用します。たとえば、ソース演算子またはアップストリーム演算子のLAST_NAME
、FIRST_NAME
およびUSERNAME
を、ターゲット・データ・エンティティのTGT_LAST_NAME
、TGT_FIRST_NAME
およびTGT_USERNAME
にマップするとします。この場合、「ソース・パターン」フィールドに*NAME
、「ターゲット・パターン」フィールドにTGT_$1
と入力します。*NAME
のアスタリスク(*)は、NAME
の前の文字列がターゲット・パターンの$1
にある文字列であることを意味します。$1
は、ソース・パターン内の最初の取得グループ(この場合はアスタリスク(*)です)を示します。
大/小文字を区別するパターン一致が必要な場合は、ソース・パターンに(?c)
接頭辞を追加します。たとえば、名前にキャメル・大文字を使用するソース属性CustomerName
を、Customer_Name
という名前のターゲット属性にマップするとします。ソース・パターンとして(?c)([A-Z][a-z]+)([A-Z][a-z]+)
を入力し、ターゲット・パターンとして$1_$2
を入力します。(?c)
接頭辞がソース・パターンの先頭に追加されると、マッピングで大/小文字を区別するパターン一致がオンになります。データ統合は、CustomerName
の"N"が新しいパターンの開始であることを検出するため、照合時に([A-Z][a-z]+)([A-Z][a-z]+)
を2つの異なる単語(取得グループ)として処理します。
ソース・リストの受信属性をターゲット・リストの属性にドラッグして、マッピングを作成します。
または、アクション・メニューから「手動マップ」を選択できます。次に、「属性のマップ」ダイアログを使用し、ソース属性およびターゲット属性を選択してマッピングを作成します。
選択したマップを削除します。
「ルールの表示」を選択します。「ルール」パネルで、1つ以上のルールを選択し、「削除」をクリックします。または、ルールのアクション・メニュー()から「削除」を選択して、そのルールを消去することもできます。
すべてのマッピングを削除します。
アクション・メニューから「マッピングのリセット」を選択します。手動ルールおよび自動マップ・ルールがすべて削除されます。
整形演算子
フィルタ演算子
フィルタ演算子を使用して、インバウンド・ポートからデータのサブセットを選択し、フィルタ条件に基づいてアウトバウンド・ポートへのダウンストリームを継続します。
条件ビルダーを使用して要素を視覚的に選択し、フィルタ条件を作成します。エディタでフィルタ条件を手動入力することもできます。
フィルタ条件を作成すると、条件に基づいてアップストリーム演算子からデータのサブセットを選択できます。
フィルタ条件で使用できる要素には、受信属性、パラメータおよび関数があります。要素をダブルクリックするかリストからドラッグしてエディタに追加し、条件を作成できます。条件は作成する前に検証できます。
「受信」には、アップストリーム演算子からこのフィルタ演算子に入る属性が表示されます。
たとえば、市区町村名でデータをフィルタするには、次のように条件式を作成します:
FILTER_1.ADDRESSES.CITY='Redwood Shores'
パラメータは、条件ビルダー(フィルタ演算子、結合演算子、ルックアップ演算子および分割演算子)または式ビルダー(式演算子および集計演算子)を使用してデータ・フローに追加された式パラメータです。式パラメータには、名前、タイプおよびデフォルト値があります。式パラメータの追加を参照してください。
たとえば、フィルタ条件で市区町村名のパラメータを使用するとします。P_VARCHAR_CITY
という名前のVARCHAR
パラメータを作成し、デフォルト値をRedwood Shores
に設定できます。その後、次のようにフィルタ式を作成できます:
FILTER_1.ADDRESSES.CITY=$P_VARCHAR_CITY
ファンクションはデータ統合で使用可能な関数で、条件の中で使用できます。ファンクションは、関数に渡される引数に対して実行される操作です。ファンクションは、引数からデータ値を計算、操作または抽出します。
たとえば、市区町村名または人口でデータをフィルタするには、OR
関数を使用して、次のようにフィルタ条件式を作成します:
FILTER_1.COUNTRIES.CITY=$P_VARCHAR_CITY OR FILTER_1.COUNTRIES.POPULATION>100000000
FILTER_1.COUNTRIES.CITY=$P_VARCHAR_CITY OR FILTER_1.COUNTRIES.POPULATION>$P_NUMERIC
条件の作成時に追加できる関数のリストを次に示します:
関数 | 説明 | 例 |
---|---|---|
MD5(all data types) | データ型のMD5 チェックサムを計算し、文字列値を返します。 | MD5(column_name) |
SHA1(all data types) | データ型のSHA-1 ハッシュ値を計算し、文字列値を返します。 | SHA1(column_name) |
SHA2(all data types, bitLength) | データ型のSHA-2 ハッシュ値を計算し、文字列値を返します。bitLength は整数です。 | SHA2 (column_name, bitLength can be set to 0 (equivalent to 256), 256, 384, or 512) 。 |
ORA_HASH(expr, [max_bucket], [seed_value]) |
Oracleは、ハッシュ関数を |
|
関数 | 説明 | 例 |
---|---|---|
ABS(numeric) | numeric 値の絶対乗を返します。 | ABS(-1) |
CEIL(numeric) | numeric 値を超えない最小の整数を返します | CEIL(-1,2) |
FLOOR(numeric) | numeric 値を超えない最大の整数を返します。 | FLOOR(-1,2) |
MOD(numeric1, numeric2) | numeric1 をnumeric2 で除算した後の剰余を返します。 | MOD(8,2) |
POWER(numeric1, numeric2) | numeric1 をnumeric2 で累乗します。 | POWER(2,3) |
ROUND(numeric1, numeric2) | numeric1 を小数点以下numeric2 桁に丸めて返します。 | ROUND(2.5,0) |
TRUNC(numeric1, numeric2) | numeric1 を小数点以下numeric2 桁に切り捨てて返します。 | TRUNC(2.5,0) |
TO_NUMBER(expr[, format, locale]) | 指定されたformat およびlocale (オプション)に基づいて、expr を数値に変換します。デフォルトのロケールはen-US です。サポートされる言語タグ。サポートされるフォーマット・パターン:
|
|
関数 | 説明 | 例 |
---|---|---|
CURRENT_DATE |
現在の日付を返します。 | CURRENT_DATE は、2023-05-26 などの今日の日付を返します |
CURRENT_TIMESTAMP |
セッション・タイムゾーンに対する現在の日付と時刻を返します。 | CURRENT_TIMESTAMP は、今日の日付と現在の時刻(2023-05-26 12:34:56 など)を返します
|
DATE_ADD(date, number_of_days) |
指定したdate からnumber 日後の日付を返します。 |
DATE_ADD('2017-07-30', 1) は2017-07-31 を返します |
DATE_FORMAT(expr, format[, locale]) |
指定された サポートされる日付フォーマット・パターン:
|
|
DAYOFMONTH(date) |
特定の日(月間)を返します。 | DAYOFMONTH('2020-12-25') は25 を返します。 |
DAYOFWEEK(date) |
特定の日(週間)を返します。 | DAYOFWEEK('2020-12-25') は、金曜日の6 を返します。米国では、日曜日は1、月曜日は2などとみなされます。 |
DAYOFYEAR(date) |
特定の日(年間)を返します。 | DAYOFYEAR('2020-12-25') は360 を返します |
WEEKOFYEAR(date) |
日付が年内の何番目の週かを返します。 |
|
HOUR(datetime) |
日時の時間の値を返します。 | HOUR('2020-12-25 15:10:30') は15 を返します |
LAST_DAY(date) |
月末の日付を返します。 | LAST_DAY('2020-12-25') は31 を返します |
MINUTE(datetime) |
日時の分の値を返します。 | HOUR('2020-12-25 15:10:30') は10 を返します |
MONTH(date) |
日付の月の値を返します。 | MONTH('2020-06-25') は6 を返します。 |
QUARTER(date) |
日付が属する四半期を返します。 | QUARTER('2020-12-25') は4 を返します。 |
SECOND(datetime) |
日時の秒の値を返します。 | SECOND('2020-12-25 15:10:30') は30 を返します |
TO_DATE(string, format_string[, localeStr]) |
format_string 式を含む文字列式を日付に解析します。ロケールはオプションです。デフォルトはen-US です。サポートされる言語タグ。パイプライン式では、
|
|
TO_TIMESTAMP(expr, format_string[, localeStr]) |
指定されたformat_string およびlocaleStr (オプション)に基づいて、VARCHARのexpr をTIMESTAMPの値に変換します。パイプライン式では、
|
TO_TIMESTAMP('2020-10-11 11:10:10', 'yyyy-MM-dd HH:mm:ss') は、11am 10:10 Oct 11th, 2020 を表すTIMESTAMPオブジェクトを返します |
WEEK(date) |
日付の週の値を返します。 |
WEEK('2020-06-25') は4 を返します |
YEAR(date) |
日付の年の値を返します。 | YEAR('2020-06-25') returns 2020 |
ADD_MONTHS(date_expr, number_months) |
指定した日付、タイムスタンプまたは文字列(yyyy-MM-dd またはyyyy-MM-dd HH:mm:ss.SSS のようなフォーマット)に指定した数の月を追加した日付を返します。 |
|
MONTHS_BETWEEN(start_date_expr, end_date_expr) |
整数が返されるのは、両方の日付の日の部分が同じ場合、または両方が月の最終日の場合です。それ以外の場合は、1か月を31日として差が計算されます。 |
|
FROM_UTC_TIMESTAMP(time_stamp, time_zone) |
日付、タイムスタンプまたは文字列をUTC時間として解釈し、その時間を指定したタイムゾーンのタイムスタンプに変換します。 文字列の場合は、 タイム・ゾーンのフォーマットは、リージョンベースのゾーンID(例: 'Asia/Seoul'のような'area/city')またはタイム・ゾーン・オフセット(例: UTC+02)のいずれかです。 |
FROM_UTC_TIMESTAMP('2017-07-14 02:40:00.0', 'GMT+1') は2017-07-14 03:40:00.0 を返します |
TO_UTC_TIMESTAMP(time_stamp, time_zone) |
指定したタイムゾーンの日付、タイムスタンプまたは文字列をUTCタイムスタンプに変換します。 文字列の場合は、 タイム・ゾーンのフォーマットは、リージョンベースのゾーンID(例: 'Asia/Seoul'のような'area/city')またはタイム・ゾーン・オフセット(例: UTC+02)のいずれかです。 |
TO_UTC_TIMESTAMP('2017-07-14 02:40:00.0', 'GMT+1') は2017-07-14 01:40:00.0 を返します
|
FROM_UNIXTIME(unix_time[, fmt]) |
指定したUnix時間すなわちエポックを、現在のシステム・タイム・ゾーンおよび指定したフォーマットで、その時点のタイムスタンプを表す文字列に変換します。 ノート: Unix timeは、1970年1月1日00:00:00 UTCから経過した秒数です。
|
例のデフォルトのタイムゾーンはPSTです |
UNIX_TIMESTAMP([time_expr[, fmt]]) |
現在時刻または指定した時刻をUnixタイムスタンプ(秒単位)に変換します。
|
この例のデフォルトのタイムゾーンはPSTです |
INTERVAL 'year' YEAR[(year_precision)] |
期間を年単位で返します。 year_precisionはyearフィールドの桁数で、範囲は0から9です。year_precisionを省略した場合、デフォルトは2です(100年未満である必要があります)。 |
|
INTERVAL 'year month' YEAR[(year_precision)] TO MONTH |
期間を年と月の単位で返します。yearおよびmonthフィールドを使用して期間を格納するために使用します。 year_precisionはyearフィールドの桁数で、範囲は0から9です。year_precisionを省略した場合、デフォルトは2です(100年未満である必要があります)。 |
INTERVAL '100-5' YEAR(3) TO MONTH は100年と5か月の期間を返します。先頭の年の精度として3を指定する必要があります。 |
INTERVAL 'month' MONTH[(month_precision)] |
期間を月単位で返します。 month_precisionはmonthフィールドの桁数で、範囲は0から9です。month_precisionを省略した場合、デフォルトは2です(100年未満である必要があります)。 |
INTERVAL '200' MONTH(3) は200か月の期間を返します。月の精度として3を指定する必要があります。 |
INTERVAL 'day time' DAY[(day_precision)] TO SECOND[(fractional_seconds_precision)] |
日、時、分、秒で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 fractional_seconds_precisionは、timeフィールドの秒の値の分数部分の桁数であり、範囲は0から9です。 |
INTERVAL '11 10:09:08.555' DAY TO SECOND(3) は、11日10時間9分8秒555ミリ秒の期間を返します |
INTERVAL 'day time' DAY[(day_precision)] TO MINUTE[(minute_precision)] |
日、時、分で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '11 10:09' DAY TO MINUTE は、11日10時間9分の期間を返します |
INTERVAL 'day time' DAY[(day_precision)] TO HOUR[(hour_precision)] |
日数と時間数で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '100 10' DAY(3) TO HOUR は、100日10時間の期間を返します |
INTERVAL 'day' DAY[(day_precision)] |
日数で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 |
INTERVAL '999' DAY(3) は999日の期間を返します |
INTERVAL 'time' HOUR[(hour_precision)] TO SECOND[(fractional_seconds_precision)] |
時、分、秒で期間を返します。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 fractional_seconds_precisionは、timeフィールドの秒の値の分数部分の桁数であり、範囲は0から9です。 |
INTERVAL '09:08:07.6666666' HOUR TO SECOND(7) は、9時間8分7.6666666秒の期間を返します |
INTERVAL 'time' HOUR[(hour_precision)] TO MINUTE[(minute_precision)] |
時間と分数で期間を返します。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '09:30' HOUR TO MINUTE は、9時間30分の期間を返します |
INTERVAL 'hour' HOUR[(hour_precision)] |
時間数で期間を返します。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '40' HOUR は40時間の期間を返します |
INTERVAL 'minute' MINUTE[(minute_precision)] |
分数で期間を返します。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '15' MINUTE は15分間の期間を返します |
INTERVAL 'time' MINUTE[(minute_precision)] TO SECOND[(fractional_seconds_precision)] |
分数と秒数で期間を返します。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 fractional_seconds_precisionは、timeフィールドの秒の値の分数部分の桁数であり、範囲は0から9です。 |
INTERVAL '15:30' MINUTE TO SECOND は15分30秒の期間を返します |
INTERVAL 'second' SECOND[(fractional_seconds_precision)] |
秒数で期間を返します。 fractional_seconds_precisionは、secondフィールドの分数部分の桁数であり、範囲は0から9です。デフォルトは3です。 |
INTERVAL '15.678' SECOND は15.678秒の期間を返します |
関数 | 説明 | 例 |
---|---|---|
FIRST_VALUE(value) OVER ([ partition_clause ] order_by_clause [ windowFrame_clause ] ) |
ウィンドウ・フレームの最初の行である行で評価された値を返します。 | FIRST_VALUE(BANK_ID) OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) は、現在行とその行の後の1行の間にある行を計算し、BANK_ID でパーティション化し、BANK_NAME で昇順にしたときの、ウィンドウ内のBANK_ID の最初の値を返します。 |
LAG(value[, offset[, default]]) OVER ([ partition_clause ] order_by_clause) |
パーティション内の現在行より前の指定されたオフセットで、行で評価された値を返します。そのような行がない場合は、デフォルト値が返されます。現在の行に基づいて、オフセットとデフォルトの両方が評価されます。省略した場合、オフセットは1に設定され、デフォルトはNULLに設定されます。 | LAG(BANK_ID, 2, 'hello') OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME DESC) は、BANK_ID でパーティション化し、BANK_NAME の降順にしたときの、現在行より2行前のBANK_ID の値を返します。そのような値がない場合は、hello が返されます。 |
LAST_VALUE(value) OVER ([ partition_clause ] order_by_clause [ windowFrame_clause ]) |
ウィンドウ・フレームの最後の行である行で評価された値を返します。 | LAST_VALUE(BANK_ID) OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) は、現在行とその行の後の1行の間にある行を計算し、BANK_ID でパーティション化し、BANK_NAME で昇順にしたときの、ウィンドウ内のBANK_ID の最後の値を返します。 |
LEAD(value[, offset[, default]]) OVER ([ partition_clause ] order_by_clause) |
パーティション内の現在行より後の特定のオフセットにある行で評価された値を返します。そのような行がない場合は、デフォルト値が返されます。現在の行に基づいて、オフセットとデフォルトの両方が評価されます。省略した場合、オフセットは1に設定され、デフォルトはNULLに設定されます。 | LEAD(BANK_ID, 2, 'hello') OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME) は、BANK_ID でパーティション化し、BANK_NAME の昇順にしたときの、現在行より2行後のBANK_ID の値を返します。そのような値がない場合は、hello が返されます。 |
RANK() OVER([ partition_clause ] order_by_clause) |
ギャップを含む現在行のランクを返します(1からカウント)。 | RANK() OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME) は、BANK_ID のパーティション・グループ内の各行のランクをBANK_NAME の昇順で返します。 |
ROW_NUMBER() OVER([ partition_clause ] order_by_clause) |
パーティション内の現在行の一意の番号を返します(1からカウント)。 | ROW_NUMBER() OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME) は、BANK_ID のパーティション・グループ内の各行の一意の行番号をBANK_NAME の昇順で返します。 |
関数 | 説明 | 例 |
---|---|---|
CAST(value AS type) | 指定されたタイプの指定された値を返します。 | CAST("10" AS INT) は10 を返します |
CONCAT(string, string) | 文字列または列を結合した値を返します | CONCAT('Oracle','SQL') はOracleSQL を返します |
CONCAT_WS(separator, expression1, expression2, expression3,...) |
指定したセパレータを使用して、文字列または列を結合した値を文字列または列間で返します。 区切り記号は必須であり、文字列である必要があります。 セパレータの後に少なくとも1つの式を指定する必要があります。たとえば: |
CONCAT_WS('-', 'Hello', 'Oracle') はHello-Oracle を返します
関数の子が配列である場合、配列はフラット化されます。
|
INITCAP(string) | 各単語の最初の文字を大文字、残りの文字をすべて小文字にした文字列を、単語どうしを空白で区切って返します。 | INITCAP('oRACLE sql') はOracle Sql を返します |
INSTR(string, substring[start_position]) | string で最初に出現するsubstring の(1から始まる)索引を返します | INSTR('OracleSQL', 'SQL') は7 を返します |
LOWER(string) | すべての文字を小文字に変更して文字列を返します。 | LOWER('ORACLE') はoracle を返します |
LENGTH(string) | 文字列の文字長またはバイナリ・データのバイト数を返します。文字列の長さには末尾のスペースも含まれます。 | LENGTH('Oracle') は6 を返します |
LTRIM(string) | 先頭のスペースを左から除去して文字列を返します。 | LTRIM(' Oracle') |
NVL(expr1, epxr2) | nullでない引数を返します。 | NVL(EXPRESSION_3.CUSTOMERS_JSON.CONTINENT_ID, ROWID()) |
REGEXP_SUBSTR(string, regexp[, RegexGroupIdx]) | 入力文字列から正規表現パターンに一致する文字列を検索して抽出します。オプションのキャプチャ・グループ索引が指定されている場合、この関数は特定のグループを抽出します。 |
REGEXP_SUBSTR('22;33;44', '([0-9.]*);([0-9.]*);([0-9.]*)', 1) は22 を返します |
REPLACE(string, search, replacement) | 出現するすべてのsearch をreplacement に置換します。文字列に
| REPLACE('ABCabc', 'abc', 'DEF') はABCDEF を返します |
RTRIM(string) | 先頭のスペースを右から除去して文字列を返します。 | RTRIM('Oracle ') |
SUBSTRING(string, position[, substring_length]) | 位置から始まる部分文字列を返します。 | SUBSTRING('Oracle SQL' FROM 2 FOR 3) はrac を返します |
数値の場合、TO_CHAR(expr) および日付の場合、TO_CHAR(expr, format[, locale]) | 数字と日付を文字列に変換します。数値の場合、書式は必要ありません。日付の場合は、「日時関数」で説明されているDATE_FORMAT と同じ形式を使用します。デフォルトのロケールはen-US です。「サポートされている言語タグ」を参照してください。パイプライン式では、
| 数値の例: 日付の例: |
UPPER(string) | すべての文字を大文字に変更して文字列を返します。 | UPPER('oracle') はORACLE を返します |
LPAD(str, len[, pad]) | 文字列の左側に指定した文字を特定の長さまで埋め込んで返します。pad文字を省略した場合、デフォルトは空白です。 | LPAD('ABC', 5, '*') は'**ABC' を返します |
RPAD(str, len[, pad]) | 文字列の右側に指定した文字を特定の長さまで埋め込んで返します。pad文字を省略した場合、デフォルトは空白です。 | RPAD('XYZ', 6, '+' )は'XYZ+++' を返します |
関数 | 説明 | 例 |
---|---|---|
CASE WHEN condition1 THEN result1 ELSE result2 END | 条件を満たす値を返します。 | CASE WHEN 1 > 0 THEN 'ABC' ELSE 'XYZ' END は、1> 0 の場合はABC を返し、それ以外の場合はXYZ を返します |
AND | 論理AND演算子。両方のオペランドがtrueの場合はtrueを返し、それ以外の場合はfalseを返します。 | (x = 10 AND y = 20)は、xが10でyが20の場合、trueを返します。いずれか一方がtrueでない場合は、falseを返します |
OR | 論理OR演算子。いずれかのオペランドがtrueであるか、両方ともtrueである場合はtrueを返し、それ以外の場合はfalseを返します。 | (x = 10 OR y = 20)は、xが10ではなく、かつyが20でない場合、falseを返します。いずれか一方がtrueの場合は、trueを返します |
NOT | 論理NOT演算子。 | |
LIKE | string1がstring2のパターンに一致するかどうかにかかわらず、文字列パターン・マッチングを実行します。 | |
= | 等価かどうかをテストします。expr1がexpr2と等しい場合はtrueを返し、それ以外の場合はfalseを返します。 | x = 10は、xの値が10の場合はtrueを返し、それ以外の場合はfalseを返します |
!= | 非等価かどうかをテストします。expr1がexpr2と等しくない場合はtrueを返し、それ以外の場合はfalseを返します。 | x != 10は、xの値が10の場合はfalseを返し、それ以外の場合はtrueを返します |
> | 式の大なりをテストします。expr1がexpr2より大きい場合は、trueを返します。 | x > 10は、xの値が10より大きい場合はtrueを返し、それ以外の場合はfalseを返します |
>= | 式の大なりイコールをテストします。expr1がexpr2以上の場合は、trueを返します。 | x > =10は、xの値が10以上の場合はtrueを返し、それ以外の場合はfalseを返します |
< | 式の小なりをテストします。expr1がexpr2より小さい場合は、trueを返します。 | x < 10は、xの値が10より小さい場合はtrueを返し、それ以外の場合はfalseを返します |
<= | 式の小なりイコールをテストします。expr1がexpr2以下の場合は、trueを返します。 | x <= 10は、xの値が10より小さい場合はtrueを返し、それ以外の場合はfalseを返します |
|| | 2つの文字列を連結します。 | 'XYZ' || 'hello' は'XYZhello' を返します |
BETWEEN | 範囲を評価します。 | FILTER_1.BANK.BANK_ID BETWEEN 1003 AND 1007 |
IN | 式が値リストと一致するかどうかをテストします。 | FILTER_2.ORDERS.ORDER_ID IN (1003, 1007) |
関数 | 説明 | 例 |
---|---|---|
NUMERIC_ID() | 各行に対して64ビットの数値の汎用一意識別子を生成します。 | NUMERIC_ID() は、たとえば3458761969522180096 および3458762008176885761 を返します |
ROWID() | 単調に増加する64ビット数を生成します。 | ROWID() は、たとえば、0 、1 、2 などを返します |
UUID() | 各行に対して128ビットの文字列の汎用一意識別子を生成します。 | UUID() は、たとえば20d45c2f-0d56-4356-8910-162f4f40fb6d を返します |
MONOTONICALLY_INCREASING_ID() |
単調に増加する一意の64ビット整数を生成します。連続した数字にはなりません。 | MONOTONICALLY_INCREASING_ID() は、たとえば、8589934592 や25769803776 を返します |
関数 | 説明 | 例 |
---|---|---|
COALESCE(value, value [, value]*) | 存在する場合は最初のnullでない引数を返し、それ以外の場合はnullを返します。 | COALESCE(NULL, 1, NULL) は1 を返します |
NULLIF(value, value) | 2つの値が等しい場合はnullを返し、それ以外の場合は最初の値を返します。 | NULLIF('ABC','XYZ') はABC を返します |
関数 | 説明 | 例 |
---|---|---|
SCHEMA_OF_JSON(string) | JSON文字列を解析し、スキーマのDDLフォーマットを推測します。 |
|
FROM_JSON(column, string) | JSON文字列を含む列を解析し、指定スキーマを含む次のいずれかの型に解析します。
|
|
TO_JSON(column) | StructまたはStruct配列あるいはMapまたはMap配列の型を含む列をJSON文字列に変換します。 | TO_JSON(TO_STRUCT('s1', TO_ARRAY(1,2,3), 's2', TO_MAP('key', 'value'))) はJSON文字列{"s1":[1,2,3],"s2":{"key":"value"}} を返します |
TO_MAP(string,column[,string,column]*) | Map型の新しい列を作成します。入力列は、キーと値のペアとしてグループ化する必要があります。入力キー列をNULLにすることはできません。すべて同じデータ型である必要があります。入力値列はすべて同じデータ型である必要があります。 |
|
TO_STRUCT(string,column[,string,column]*) | Struct型の新しい列を作成します。入力列は、キーと値のペアとしてグループ化する必要があります。 |
|
TO_ARRAY(column[,column]*) | Array型の新しい列を作成します。入力列はすべて同じデータ型である必要があります。 |
|
式および階層データ型の作成をサポートするデータ・フロー演算子では、上位の関数を使用できます。
サポートされている演算子は次のとおりです:
-
集計
-
式
-
フィルタ
-
結合
-
検索
-
分割済
-
ピボット
関数 | 説明 | 例 |
---|---|---|
TRANSFORM(column, lambda_function) | 配列と無名関数を取得し、各要素に関数を適用して結果を出力配列に割り当てることによって、新しい配列を設定します。 | 整数[1, 2, 3] の入力配列の場合、TRANSFORM(array, x -> x + 1) は[2, 3, 4] の新しい配列を返します。 |
TRANSFORM_KEYS(column, lambda_function) | 2つの引数(キーと値)を持つマップと関数を取得し、キーがラムダ関数の結果のタイプを持ち、値が列マップ値のタイプを持つマップを返します。 | 整数キーおよび文字列値が{1 -> 'value1', 2 -> 'value2', 3 -> 'value3'} の入力マップの場合、TRANSFORM_KEYS(map, (k, v) -> k * 2 + 1) は{3 -> 'value1', 5 -> 'value2', 7 -> 'value3'} の新しいマップを返します。 |
TRANSFORM_VALUES(column, lambda_function) | 2つの引数(キーと値)を持つマップと関数を取得し、値がラムダ関数の結果のタイプを持ち、キーが列マップ・キーのタイプを持つマップを返します。 | 文字列キーおよび文字列値が{'a' -> 'value1', 'b' -> 'value2', 'c' -> 'value3'} の入力マップの場合、TRANSFORM_VALUES(map, (k, v) -> k || __ '' || v) は{'a' -> 'a_value1', 'b' -> 'b_value2', 'c' -> 'c_value3'} の新しいマップを返します。 |
ARRAY_SORT(array(...), lambda_function) |
式演算子のみが 配列を取得し、2つの引数を取る指定された関数に従ってソートします。 この関数は、最初のエレメントが2番目のエレメントより小さいか、等しいか、または大きいかに応じて-1、0、または1を返す必要があります。 関数を省略すると、配列は昇順にソートされます。 |
返される配列は次のとおりです。 [1,5,6] |
結合演算子
結合演算子を使用して、複数のインバウンド・ソースからのデータをリンクします。
条件ビルダーを使用して要素を視覚的に選択し、結合条件を作成します。エディタで結合条件を手動入力することもできます。
結合条件を作成すると、条件に基づいて2つのインバウンド・ソースからデータを選択できます。
結合条件で使用できる要素には、受信属性、パラメータおよび関数があります。要素をダブルクリックするかリストからドラッグしてエディタに追加し、条件を作成できます。条件は作成する前に検証できます。
「受信」には、結合演算子に接続されているアップストリーム・ポートからの属性が、2つの個別のJOINフォルダとして表示されます。各ポートからの属性を表示するには、該当するJOINフォルダを展開または縮小します。たとえば、JOIN_1_1やJOIN_1_2です。
JOIN_1_1.BANK_CUSTOMER.ADDRESS_ID = JOIN_1_2.BANK_ADDRESS.ADDRESS_ID
パラメータは、条件ビルダー(フィルタ演算子、結合演算子、ルックアップ演算子および分割演算子)または式ビルダー(式演算子および集計演算子)を使用してデータ・フローに追加された式パラメータです。式パラメータには、名前、タイプおよびデフォルト値があります。式パラメータの追加を参照してください。
2つのソースを結合し、BANK_NAME='ABC Bank'
の行のみを保持するとします。P_VARCHAR
という名前のVARCHAR
パラメータを作成し、デフォルト値をABC BANK
に設定できます。その後、次のように結合式を作成できます:
JOIN_1_1.ADDRESSES.BANK_ID = JOIN_1_2.BANK.BANK_ID AND JOIN_1_2.BANK.BANK_NAME = $P_VARCHAR
ファンクションはデータ統合で使用可能な関数で、条件の中で使用できます。ファンクションは、関数に渡される引数に対して実行される操作です。ファンクションは、引数からデータ値を計算、操作または抽出します。
条件の作成時に追加できる関数のリストを次に示します:
関数 | 説明 | 例 |
---|---|---|
MD5(all data types) | データ型のMD5 チェックサムを計算し、文字列値を返します。 | MD5(column_name) |
SHA1(all data types) | データ型のSHA-1 ハッシュ値を計算し、文字列値を返します。 | SHA1(column_name) |
SHA2(all data types, bitLength) | データ型のSHA-2 ハッシュ値を計算し、文字列値を返します。bitLength は整数です。 | SHA2 (column_name, bitLength can be set to 0 (equivalent to 256), 256, 384, or 512) 。 |
ORA_HASH(expr, [max_bucket], [seed_value]) |
Oracleは、ハッシュ関数を |
|
関数 | 説明 | 例 |
---|---|---|
ABS(numeric) | numeric 値の絶対乗を返します。 | ABS(-1) |
CEIL(numeric) | numeric 値を超えない最小の整数を返します | CEIL(-1,2) |
FLOOR(numeric) | numeric 値を超えない最大の整数を返します。 | FLOOR(-1,2) |
MOD(numeric1, numeric2) | numeric1 をnumeric2 で除算した後の剰余を返します。 | MOD(8,2) |
POWER(numeric1, numeric2) | numeric1 をnumeric2 で累乗します。 | POWER(2,3) |
ROUND(numeric1, numeric2) | numeric1 を小数点以下numeric2 桁に丸めて返します。 | ROUND(2.5,0) |
TRUNC(numeric1, numeric2) | numeric1 を小数点以下numeric2 桁に切り捨てて返します。 | TRUNC(2.5,0) |
TO_NUMBER(expr[, format, locale]) | 指定されたformat およびlocale (オプション)に基づいて、expr を数値に変換します。デフォルトのロケールはen-US です。サポートされる言語タグ。サポートされるフォーマット・パターン:
|
|
関数 | 説明 | 例 |
---|---|---|
CURRENT_DATE |
現在の日付を返します。 | CURRENT_DATE は、2023-05-26 などの今日の日付を返します |
CURRENT_TIMESTAMP |
セッション・タイムゾーンに対する現在の日付と時刻を返します。 | CURRENT_TIMESTAMP は、今日の日付と現在の時刻(2023-05-26 12:34:56 など)を返します
|
DATE_ADD(date, number_of_days) |
指定したdate からnumber 日後の日付を返します。 |
DATE_ADD('2017-07-30', 1) は2017-07-31 を返します |
DATE_FORMAT(expr, format[, locale]) |
指定された サポートされる日付フォーマット・パターン:
|
|
DAYOFMONTH(date) |
特定の日(月間)を返します。 | DAYOFMONTH('2020-12-25') は25 を返します。 |
DAYOFWEEK(date) |
特定の日(週間)を返します。 | DAYOFWEEK('2020-12-25') は、金曜日の6 を返します。米国では、日曜日は1、月曜日は2などとみなされます。 |
DAYOFYEAR(date) |
特定の日(年間)を返します。 | DAYOFYEAR('2020-12-25') は360 を返します |
WEEKOFYEAR(date) |
日付が年内の何番目の週かを返します。 |
|
HOUR(datetime) |
日時の時間の値を返します。 | HOUR('2020-12-25 15:10:30') は15 を返します |
LAST_DAY(date) |
月末の日付を返します。 | LAST_DAY('2020-12-25') は31 を返します |
MINUTE(datetime) |
日時の分の値を返します。 | HOUR('2020-12-25 15:10:30') は10 を返します |
MONTH(date) |
日付の月の値を返します。 | MONTH('2020-06-25') は6 を返します。 |
QUARTER(date) |
日付が属する四半期を返します。 | QUARTER('2020-12-25') は4 を返します。 |
SECOND(datetime) |
日時の秒の値を返します。 | SECOND('2020-12-25 15:10:30') は30 を返します |
TO_DATE(string, format_string[, localeStr]) |
format_string 式を含む文字列式を日付に解析します。ロケールはオプションです。デフォルトはen-US です。サポートされる言語タグ。パイプライン式では、
|
|
TO_TIMESTAMP(expr, format_string[, localeStr]) |
指定されたformat_string およびlocaleStr (オプション)に基づいて、VARCHARのexpr をTIMESTAMPの値に変換します。パイプライン式では、
|
TO_TIMESTAMP('2020-10-11 11:10:10', 'yyyy-MM-dd HH:mm:ss') は、11am 10:10 Oct 11th, 2020 を表すTIMESTAMPオブジェクトを返します |
WEEK(date) |
日付の週の値を返します。 |
WEEK('2020-06-25') は4 を返します |
YEAR(date) |
日付の年の値を返します。 | YEAR('2020-06-25') returns 2020 |
ADD_MONTHS(date_expr, number_months) |
指定した日付、タイムスタンプまたは文字列(yyyy-MM-dd またはyyyy-MM-dd HH:mm:ss.SSS のようなフォーマット)に指定した数の月を追加した日付を返します。 |
|
MONTHS_BETWEEN(start_date_expr, end_date_expr) |
整数が返されるのは、両方の日付の日の部分が同じ場合、または両方が月の最終日の場合です。それ以外の場合は、1か月を31日として差が計算されます。 |
|
FROM_UTC_TIMESTAMP(time_stamp, time_zone) |
日付、タイムスタンプまたは文字列をUTC時間として解釈し、その時間を指定したタイムゾーンのタイムスタンプに変換します。 文字列の場合は、 タイム・ゾーンのフォーマットは、リージョンベースのゾーンID(例: 'Asia/Seoul'のような'area/city')またはタイム・ゾーン・オフセット(例: UTC+02)のいずれかです。 |
FROM_UTC_TIMESTAMP('2017-07-14 02:40:00.0', 'GMT+1') は2017-07-14 03:40:00.0 を返します |
TO_UTC_TIMESTAMP(time_stamp, time_zone) |
指定したタイムゾーンの日付、タイムスタンプまたは文字列をUTCタイムスタンプに変換します。 文字列の場合は、 タイム・ゾーンのフォーマットは、リージョンベースのゾーンID(例: 'Asia/Seoul'のような'area/city')またはタイム・ゾーン・オフセット(例: UTC+02)のいずれかです。 |
TO_UTC_TIMESTAMP('2017-07-14 02:40:00.0', 'GMT+1') は2017-07-14 01:40:00.0 を返します
|
FROM_UNIXTIME(unix_time[, fmt]) |
指定したUnix時間すなわちエポックを、現在のシステム・タイム・ゾーンおよび指定したフォーマットで、その時点のタイムスタンプを表す文字列に変換します。 ノート: Unix timeは、1970年1月1日00:00:00 UTCから経過した秒数です。
|
例のデフォルトのタイムゾーンはPSTです |
UNIX_TIMESTAMP([time_expr[, fmt]]) |
現在時刻または指定した時刻をUnixタイムスタンプ(秒単位)に変換します。
|
この例のデフォルトのタイムゾーンはPSTです |
INTERVAL 'year' YEAR[(year_precision)] |
期間を年単位で返します。 year_precisionはyearフィールドの桁数で、範囲は0から9です。year_precisionを省略した場合、デフォルトは2です(100年未満である必要があります)。 |
|
INTERVAL 'year month' YEAR[(year_precision)] TO MONTH |
期間を年と月の単位で返します。yearおよびmonthフィールドを使用して期間を格納するために使用します。 year_precisionはyearフィールドの桁数で、範囲は0から9です。year_precisionを省略した場合、デフォルトは2です(100年未満である必要があります)。 |
INTERVAL '100-5' YEAR(3) TO MONTH は100年と5か月の期間を返します。先頭の年の精度として3を指定する必要があります。 |
INTERVAL 'month' MONTH[(month_precision)] |
期間を月単位で返します。 month_precisionはmonthフィールドの桁数で、範囲は0から9です。month_precisionを省略した場合、デフォルトは2です(100年未満である必要があります)。 |
INTERVAL '200' MONTH(3) は200か月の期間を返します。月の精度として3を指定する必要があります。 |
INTERVAL 'day time' DAY[(day_precision)] TO SECOND[(fractional_seconds_precision)] |
日、時、分、秒で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 fractional_seconds_precisionは、timeフィールドの秒の値の分数部分の桁数であり、範囲は0から9です。 |
INTERVAL '11 10:09:08.555' DAY TO SECOND(3) は、11日10時間9分8秒555ミリ秒の期間を返します |
INTERVAL 'day time' DAY[(day_precision)] TO MINUTE[(minute_precision)] |
日、時、分で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '11 10:09' DAY TO MINUTE は、11日10時間9分の期間を返します |
INTERVAL 'day time' DAY[(day_precision)] TO HOUR[(hour_precision)] |
日数と時間数で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '100 10' DAY(3) TO HOUR は、100日10時間の期間を返します |
INTERVAL 'day' DAY[(day_precision)] |
日数で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 |
INTERVAL '999' DAY(3) は999日の期間を返します |
INTERVAL 'time' HOUR[(hour_precision)] TO SECOND[(fractional_seconds_precision)] |
時、分、秒で期間を返します。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 fractional_seconds_precisionは、timeフィールドの秒の値の分数部分の桁数であり、範囲は0から9です。 |
INTERVAL '09:08:07.6666666' HOUR TO SECOND(7) は、9時間8分7.6666666秒の期間を返します |
INTERVAL 'time' HOUR[(hour_precision)] TO MINUTE[(minute_precision)] |
時間と分数で期間を返します。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '09:30' HOUR TO MINUTE は、9時間30分の期間を返します |
INTERVAL 'hour' HOUR[(hour_precision)] |
時間数で期間を返します。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '40' HOUR は40時間の期間を返します |
INTERVAL 'minute' MINUTE[(minute_precision)] |
分数で期間を返します。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '15' MINUTE は15分間の期間を返します |
INTERVAL 'time' MINUTE[(minute_precision)] TO SECOND[(fractional_seconds_precision)] |
分数と秒数で期間を返します。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 fractional_seconds_precisionは、timeフィールドの秒の値の分数部分の桁数であり、範囲は0から9です。 |
INTERVAL '15:30' MINUTE TO SECOND は15分30秒の期間を返します |
INTERVAL 'second' SECOND[(fractional_seconds_precision)] |
秒数で期間を返します。 fractional_seconds_precisionは、secondフィールドの分数部分の桁数であり、範囲は0から9です。デフォルトは3です。 |
INTERVAL '15.678' SECOND は15.678秒の期間を返します |
関数 | 説明 | 例 |
---|---|---|
FIRST_VALUE(value) OVER ([ partition_clause ] order_by_clause [ windowFrame_clause ] ) |
ウィンドウ・フレームの最初の行である行で評価された値を返します。 | FIRST_VALUE(BANK_ID) OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) は、現在行とその行の後の1行の間にある行を計算し、BANK_ID でパーティション化し、BANK_NAME で昇順にしたときの、ウィンドウ内のBANK_ID の最初の値を返します。 |
LAG(value[, offset[, default]]) OVER ([ partition_clause ] order_by_clause) |
パーティション内の現在行より前の指定されたオフセットで、行で評価された値を返します。そのような行がない場合は、デフォルト値が返されます。現在の行に基づいて、オフセットとデフォルトの両方が評価されます。省略した場合、オフセットは1に設定され、デフォルトはNULLに設定されます。 | LAG(BANK_ID, 2, 'hello') OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME DESC) は、BANK_ID でパーティション化し、BANK_NAME の降順にしたときの、現在行より2行前のBANK_ID の値を返します。そのような値がない場合は、hello が返されます。 |
LAST_VALUE(value) OVER ([ partition_clause ] order_by_clause [ windowFrame_clause ]) |
ウィンドウ・フレームの最後の行である行で評価された値を返します。 | LAST_VALUE(BANK_ID) OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) は、現在行とその行の後の1行の間にある行を計算し、BANK_ID でパーティション化し、BANK_NAME で昇順にしたときの、ウィンドウ内のBANK_ID の最後の値を返します。 |
LEAD(value[, offset[, default]]) OVER ([ partition_clause ] order_by_clause) |
パーティション内の現在行より後の特定のオフセットにある行で評価された値を返します。そのような行がない場合は、デフォルト値が返されます。現在の行に基づいて、オフセットとデフォルトの両方が評価されます。省略した場合、オフセットは1に設定され、デフォルトはNULLに設定されます。 | LEAD(BANK_ID, 2, 'hello') OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME) は、BANK_ID でパーティション化し、BANK_NAME の昇順にしたときの、現在行より2行後のBANK_ID の値を返します。そのような値がない場合は、hello が返されます。 |
RANK() OVER([ partition_clause ] order_by_clause) |
ギャップを含む現在行のランクを返します(1からカウント)。 | RANK() OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME) は、BANK_ID のパーティション・グループ内の各行のランクをBANK_NAME の昇順で返します。 |
ROW_NUMBER() OVER([ partition_clause ] order_by_clause) |
パーティション内の現在行の一意の番号を返します(1からカウント)。 | ROW_NUMBER() OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME) は、BANK_ID のパーティション・グループ内の各行の一意の行番号をBANK_NAME の昇順で返します。 |
関数 | 説明 | 例 |
---|---|---|
CAST(value AS type) | 指定されたタイプの指定された値を返します。 | CAST("10" AS INT) は10 を返します |
CONCAT(string, string) | 文字列または列を結合した値を返します | CONCAT('Oracle','SQL') はOracleSQL を返します |
CONCAT_WS(separator, expression1, expression2, expression3,...) |
指定したセパレータを使用して、文字列または列を結合した値を文字列または列間で返します。 区切り記号は必須であり、文字列である必要があります。 セパレータの後に少なくとも1つの式を指定する必要があります。たとえば: |
CONCAT_WS('-', 'Hello', 'Oracle') はHello-Oracle を返します
関数の子が配列である場合、配列はフラット化されます。
|
INITCAP(string) | 各単語の最初の文字を大文字、残りの文字をすべて小文字にした文字列を、単語どうしを空白で区切って返します。 | INITCAP('oRACLE sql') はOracle Sql を返します |
INSTR(string, substring[start_position]) | string で最初に出現するsubstring の(1から始まる)索引を返します | INSTR('OracleSQL', 'SQL') は7 を返します |
LOWER(string) | すべての文字を小文字に変更して文字列を返します。 | LOWER('ORACLE') はoracle を返します |
LENGTH(string) | 文字列の文字長またはバイナリ・データのバイト数を返します。文字列の長さには末尾のスペースも含まれます。 | LENGTH('Oracle') は6 を返します |
LTRIM(string) | 先頭のスペースを左から除去して文字列を返します。 | LTRIM(' Oracle') |
NVL(expr1, epxr2) | nullでない引数を返します。 | NVL(EXPRESSION_3.CUSTOMERS_JSON.CONTINENT_ID, ROWID()) |
REGEXP_SUBSTR(string, regexp[, RegexGroupIdx]) | 入力文字列から正規表現パターンに一致する文字列を検索して抽出します。オプションのキャプチャ・グループ索引が指定されている場合、この関数は特定のグループを抽出します。 |
REGEXP_SUBSTR('22;33;44', '([0-9.]*);([0-9.]*);([0-9.]*)', 1) は22 を返します |
REPLACE(string, search, replacement) | 出現するすべてのsearch をreplacement に置換します。文字列に
| REPLACE('ABCabc', 'abc', 'DEF') はABCDEF を返します |
RTRIM(string) | 先頭のスペースを右から除去して文字列を返します。 | RTRIM('Oracle ') |
SUBSTRING(string, position[, substring_length]) | 位置から始まる部分文字列を返します。 | SUBSTRING('Oracle SQL' FROM 2 FOR 3) はrac を返します |
数値の場合、TO_CHAR(expr) および日付の場合、TO_CHAR(expr, format[, locale]) | 数字と日付を文字列に変換します。数値の場合、書式は必要ありません。日付の場合は、「日時関数」で説明されているDATE_FORMAT と同じ形式を使用します。デフォルトのロケールはen-US です。「サポートされている言語タグ」を参照してください。パイプライン式では、
| 数値の例: 日付の例: |
UPPER(string) | すべての文字を大文字に変更して文字列を返します。 | UPPER('oracle') はORACLE を返します |
LPAD(str, len[, pad]) | 文字列の左側に指定した文字を特定の長さまで埋め込んで返します。pad文字を省略した場合、デフォルトは空白です。 | LPAD('ABC', 5, '*') は'**ABC' を返します |
RPAD(str, len[, pad]) | 文字列の右側に指定した文字を特定の長さまで埋め込んで返します。pad文字を省略した場合、デフォルトは空白です。 | RPAD('XYZ', 6, '+' )は'XYZ+++' を返します |
関数 | 説明 | 例 |
---|---|---|
CASE WHEN condition1 THEN result1 ELSE result2 END | 条件を満たす値を返します。 | CASE WHEN 1 > 0 THEN 'ABC' ELSE 'XYZ' END は、1> 0 の場合はABC を返し、それ以外の場合はXYZ を返します |
AND | 論理AND演算子。両方のオペランドがtrueの場合はtrueを返し、それ以外の場合はfalseを返します。 | (x = 10 AND y = 20)は、xが10でyが20の場合、trueを返します。いずれか一方がtrueでない場合は、falseを返します |
OR | 論理OR演算子。いずれかのオペランドがtrueであるか、両方ともtrueである場合はtrueを返し、それ以外の場合はfalseを返します。 | (x = 10 OR y = 20)は、xが10ではなく、かつyが20でない場合、falseを返します。いずれか一方がtrueの場合は、trueを返します |
NOT | 論理NOT演算子。 | |
LIKE | string1がstring2のパターンに一致するかどうかにかかわらず、文字列パターン・マッチングを実行します。 | |
= | 等価かどうかをテストします。expr1がexpr2と等しい場合はtrueを返し、それ以外の場合はfalseを返します。 | x = 10は、xの値が10の場合はtrueを返し、それ以外の場合はfalseを返します |
!= | 非等価かどうかをテストします。expr1がexpr2と等しくない場合はtrueを返し、それ以外の場合はfalseを返します。 | x != 10は、xの値が10の場合はfalseを返し、それ以外の場合はtrueを返します |
> | 式の大なりをテストします。expr1がexpr2より大きい場合は、trueを返します。 | x > 10は、xの値が10より大きい場合はtrueを返し、それ以外の場合はfalseを返します |
>= | 式の大なりイコールをテストします。expr1がexpr2以上の場合は、trueを返します。 | x > =10は、xの値が10以上の場合はtrueを返し、それ以外の場合はfalseを返します |
< | 式の小なりをテストします。expr1がexpr2より小さい場合は、trueを返します。 | x < 10は、xの値が10より小さい場合はtrueを返し、それ以外の場合はfalseを返します |
<= | 式の小なりイコールをテストします。expr1がexpr2以下の場合は、trueを返します。 | x <= 10は、xの値が10より小さい場合はtrueを返し、それ以外の場合はfalseを返します |
|| | 2つの文字列を連結します。 | 'XYZ' || 'hello' は'XYZhello' を返します |
BETWEEN | 範囲を評価します。 | FILTER_1.BANK.BANK_ID BETWEEN 1003 AND 1007 |
IN | 式が値リストと一致するかどうかをテストします。 | FILTER_2.ORDERS.ORDER_ID IN (1003, 1007) |
関数 | 説明 | 例 |
---|---|---|
NUMERIC_ID() | 各行に対して64ビットの数値の汎用一意識別子を生成します。 | NUMERIC_ID() は、たとえば3458761969522180096 および3458762008176885761 を返します |
ROWID() | 単調に増加する64ビット数を生成します。 | ROWID() は、たとえば、0 、1 、2 などを返します |
UUID() | 各行に対して128ビットの文字列の汎用一意識別子を生成します。 | UUID() は、たとえば20d45c2f-0d56-4356-8910-162f4f40fb6d を返します |
MONOTONICALLY_INCREASING_ID() |
単調に増加する一意の64ビット整数を生成します。連続した数字にはなりません。 | MONOTONICALLY_INCREASING_ID() は、たとえば、8589934592 や25769803776 を返します |
関数 | 説明 | 例 |
---|---|---|
COALESCE(value, value [, value]*) | 存在する場合は最初のnullでない引数を返し、それ以外の場合はnullを返します。 | COALESCE(NULL, 1, NULL) は1 を返します |
NULLIF(value, value) | 2つの値が等しい場合はnullを返し、それ以外の場合は最初の値を返します。 | NULLIF('ABC','XYZ') はABC を返します |
関数 | 説明 | 例 |
---|---|---|
SCHEMA_OF_JSON(string) | JSON文字列を解析し、スキーマのDDLフォーマットを推測します。 |
|
FROM_JSON(column, string) | JSON文字列を含む列を解析し、指定スキーマを含む次のいずれかの型に解析します。
|
|
TO_JSON(column) | StructまたはStruct配列あるいはMapまたはMap配列の型を含む列をJSON文字列に変換します。 | TO_JSON(TO_STRUCT('s1', TO_ARRAY(1,2,3), 's2', TO_MAP('key', 'value'))) はJSON文字列{"s1":[1,2,3],"s2":{"key":"value"}} を返します |
TO_MAP(string,column[,string,column]*) | Map型の新しい列を作成します。入力列は、キーと値のペアとしてグループ化する必要があります。入力キー列をNULLにすることはできません。すべて同じデータ型である必要があります。入力値列はすべて同じデータ型である必要があります。 |
|
TO_STRUCT(string,column[,string,column]*) | Struct型の新しい列を作成します。入力列は、キーと値のペアとしてグループ化する必要があります。 |
|
TO_ARRAY(column[,column]*) | Array型の新しい列を作成します。入力列はすべて同じデータ型である必要があります。 |
|
式および階層データ型の作成をサポートするデータ・フロー演算子では、上位の関数を使用できます。
サポートされている演算子は次のとおりです:
-
集計
-
式
-
フィルタ
-
結合
-
検索
-
分割済
-
ピボット
関数 | 説明 | 例 |
---|---|---|
TRANSFORM(column, lambda_function) | 配列と無名関数を取得し、各要素に関数を適用して結果を出力配列に割り当てることによって、新しい配列を設定します。 | 整数[1, 2, 3] の入力配列の場合、TRANSFORM(array, x -> x + 1) は[2, 3, 4] の新しい配列を返します。 |
TRANSFORM_KEYS(column, lambda_function) | 2つの引数(キーと値)を持つマップと関数を取得し、キーがラムダ関数の結果のタイプを持ち、値が列マップ値のタイプを持つマップを返します。 | 整数キーおよび文字列値が{1 -> 'value1', 2 -> 'value2', 3 -> 'value3'} の入力マップの場合、TRANSFORM_KEYS(map, (k, v) -> k * 2 + 1) は{3 -> 'value1', 5 -> 'value2', 7 -> 'value3'} の新しいマップを返します。 |
TRANSFORM_VALUES(column, lambda_function) | 2つの引数(キーと値)を持つマップと関数を取得し、値がラムダ関数の結果のタイプを持ち、キーが列マップ・キーのタイプを持つマップを返します。 | 文字列キーおよび文字列値が{'a' -> 'value1', 'b' -> 'value2', 'c' -> 'value3'} の入力マップの場合、TRANSFORM_VALUES(map, (k, v) -> k || __ '' || v) は{'a' -> 'a_value1', 'b' -> 'b_value2', 'c' -> 'c_value3'} の新しいマップを返します。 |
ARRAY_SORT(array(...), lambda_function) |
式演算子のみが 配列を取得し、2つの引数を取る指定された関数に従ってソートします。 この関数は、最初のエレメントが2番目のエレメントより小さいか、等しいか、または大きいかに応じて-1、0、または1を返す必要があります。 関数を省略すると、配列は昇順にソートされます。 |
返される配列は次のとおりです。 [1,5,6] |
式演算子
式演算子を使用して、単一のデータ行に対して1つ以上の変換を実行することにより、新しい派生フィールドを作成します。
式演算子を使用して属性グループのデータ型を変更するには、属性グループのデータ型の変更を参照してください。
式演算子を使用すると、一括変換アクションを使用して属性のデータ型を変更できます。
式ビルダーを使用して要素を視覚的に選択し、エディタで式を作成します。式を自ら手動で記述することもできます。
式を作成するときは、次の点に注意してください:
文字列リテラルは単一引用符で囲みます。例:
CONCAT('We ', 'like')
およびUPPER('oracle')
。属性名は二重引用符で囲みます。例:
UPPER("Sales")
およびCONCAT(CONCAT("EXPRESSION_1.EMP_UTF8_EN_COL_CSV.EN_NAME", ' '), "EXPRESSION_1.EMP_UTF8_EN_COL_CSV.NAME")
。属性名を二重引用符で囲むことは、マルチバイト文字や、完全修飾名に特殊文字が含まれる名前では必須です。
「式の追加」パネルには、2つのセクション(「式情報」と「式ビルダー」)があります。「式情報」フィールドでは、式の名前とデータ型を指定できます。複数の属性に適用する式を作成することもできます。ネストされた型のレベルが含まれる、Map、Array、Structなどの複合データ型を扱う際には、入力した式からビルダーがデータ型を検出するように選択できます。データ型をビルダーで推測するときは、データ型をプレビューおよびリフレッシュし、式を検証できます。
「式ビルダー」セクションには、式を作成するための要素がリストされます。式で使用できる要素には、受信属性、パラメータおよび関数があります。リストの要素をダブルクリックするかリストからエディタにドラッグして式を作成するか、自分で式を記述します。式は作成する前に検証できます。
「受信」には、アップストリーム演算子からこの式演算子に入る属性が表示されます。属性リストの下には、除外ルールを適用するためのチェック・ボックスがあります。この式の出力から1つ以上の受信属性を除外するには、「受信属性の除外」チェック・ボックスを選択します。次に、メニューを使用して、出力から除外する受信属性を追加します。除外ルールが、除外対象として選択した各属性に適用されます。属性を除外できるのは、式を初めて追加するときだけです。式を編集する場合、「受信属性の除外」チェック・ボックスは使用できません。
パラメータには、ユーザー定義パラメータおよびシステム生成パラメータが含まれます。
ユーザー定義パラメータは、条件ビルダー(フィルタ演算子、結合演算子、ルックアップ演算子および分割演算子)または式ビルダー(式演算子および集計演算子)を使用してデータ・フローに追加された式パラメータです。式パラメータの追加を参照してください。構文は$PARAMETER_NAME
です。例: EXPRESSION_1.ADDRESSES.POSTAL_CODE=$P_CODE
データ統合によって、システム・パラメータ(SYS.TASK_START_TIME
など)が生成されます。システム・パラメータの値は、システム情報を記録するために式で使用できます。構文は${SYSTEM_PARAMETER}
です。例: ${SYS.TASK_RUN_NAME}
ファンクションはデータ統合で使用可能な関数で、式の中で使用できます。ファンクションは、関数に渡される引数に対して実行される操作です。ファンクションは、引数からデータ値を計算、操作または抽出します。ワークスペースで作成したユーザー定義関数を追加することもできます。例: MYLIBRARY.MYFUNCTION
データ統合で使用可能な関数のリストを次に示します:
関数 | 説明 | 例 |
---|---|---|
MD5(all data types) | データ型のMD5 チェックサムを計算し、文字列値を返します。 | MD5(column_name) |
SHA1(all data types) | データ型のSHA-1 ハッシュ値を計算し、文字列値を返します。 | SHA1(column_name) |
SHA2(all data types, bitLength) | データ型のSHA-2 ハッシュ値を計算し、文字列値を返します。bitLength は整数です。 | SHA2 (column_name, bitLength can be set to 0 (equivalent to 256), 256, 384, or 512) 。 |
ORA_HASH(expr, [max_bucket], [seed_value]) |
Oracleは、ハッシュ関数を |
|
関数 | 説明 | 例 |
---|---|---|
ABS(numeric) | numeric 値の絶対乗を返します。 | ABS(-1) |
CEIL(numeric) | numeric 値を超えない最小の整数を返します | CEIL(-1,2) |
FLOOR(numeric) | numeric 値を超えない最大の整数を返します。 | FLOOR(-1,2) |
MOD(numeric1, numeric2) | numeric1 をnumeric2 で除算した後の剰余を返します。 | MOD(8,2) |
POWER(numeric1, numeric2) | numeric1 をnumeric2 で累乗します。 | POWER(2,3) |
ROUND(numeric1, numeric2) | numeric1 を小数点以下numeric2 桁に丸めて返します。 | ROUND(2.5,0) |
TRUNC(numeric1, numeric2) | numeric1 を小数点以下numeric2 桁に切り捨てて返します。 | TRUNC(2.5,0) |
TO_NUMBER(expr[, format, locale]) | 指定されたformat およびlocale (オプション)に基づいて、expr を数値に変換します。デフォルトのロケールはen-US です。サポートされる言語タグ。サポートされるフォーマット・パターン:
|
|
式演算子のみが配列関数をサポートします。
関数 | 説明 | 例 |
---|---|---|
ARRAY_POSITION(array(...), element) | 指定された配列内で指定された要素の最初の出現位置を返します。 位置はゼロベースではなく、1で始まります。 | ARRAY_POSITION(array(3, 2, 1, 4, 1), 1) は3 を返します |
REVERSE(array(...)) |
指定された要素の配列を逆の順序で返します。 | REVERSE(array(2, 1, 4, 3)) は[3,4,1,2] を返します |
ELEMENT_AT(array(...), index) |
指定されたインデックス位置で指定された配列の要素を返します。 インデックスはゼロではなく、1で始まります。
|
ELEMENT_AT(array(1, 2, 3), 2) は2 を返します |
関数 | 説明 | 例 |
---|---|---|
CURRENT_DATE |
現在の日付を返します。 | CURRENT_DATE は、2023-05-26 などの今日の日付を返します |
CURRENT_TIMESTAMP |
セッション・タイムゾーンに対する現在の日付と時刻を返します。 | CURRENT_TIMESTAMP は、今日の日付と現在の時刻(2023-05-26 12:34:56 など)を返します
|
DATE_ADD(date, number_of_days) |
指定したdate からnumber 日後の日付を返します。 |
DATE_ADD('2017-07-30', 1) は2017-07-31 を返します |
DATE_FORMAT(expr, format[, locale]) |
指定された サポートされる日付フォーマット・パターン:
|
|
DAYOFMONTH(date) |
特定の日(月間)を返します。 | DAYOFMONTH('2020-12-25') は25 を返します。 |
DAYOFWEEK(date) |
特定の日(週間)を返します。 | DAYOFWEEK('2020-12-25') は、金曜日の6 を返します。米国では、日曜日は1、月曜日は2などとみなされます。 |
DAYOFYEAR(date) |
特定の日(年間)を返します。 | DAYOFYEAR('2020-12-25') は360 を返します |
WEEKOFYEAR(date) |
日付が年内の何番目の週かを返します。 |
|
HOUR(datetime) |
日時の時間の値を返します。 | HOUR('2020-12-25 15:10:30') は15 を返します |
LAST_DAY(date) |
月末の日付を返します。 | LAST_DAY('2020-12-25') は31 を返します |
MINUTE(datetime) |
日時の分の値を返します。 | HOUR('2020-12-25 15:10:30') は10 を返します |
MONTH(date) |
日付の月の値を返します。 | MONTH('2020-06-25') は6 を返します。 |
QUARTER(date) |
日付が属する四半期を返します。 | QUARTER('2020-12-25') は4 を返します。 |
SECOND(datetime) |
日時の秒の値を返します。 | SECOND('2020-12-25 15:10:30') は30 を返します |
TO_DATE(string, format_string[, localeStr]) |
format_string 式を含む文字列式を日付に解析します。ロケールはオプションです。デフォルトはen-US です。サポートされる言語タグ。パイプライン式では、
|
|
TO_TIMESTAMP(expr, format_string[, localeStr]) |
指定されたformat_string およびlocaleStr (オプション)に基づいて、VARCHARのexpr をTIMESTAMPの値に変換します。パイプライン式では、
|
TO_TIMESTAMP('2020-10-11 11:10:10', 'yyyy-MM-dd HH:mm:ss') は、11am 10:10 Oct 11th, 2020 を表すTIMESTAMPオブジェクトを返します |
WEEK(date) |
日付の週の値を返します。 |
WEEK('2020-06-25') は4 を返します |
YEAR(date) |
日付の年の値を返します。 | YEAR('2020-06-25') returns 2020 |
ADD_MONTHS(date_expr, number_months) |
指定した日付、タイムスタンプまたは文字列(yyyy-MM-dd またはyyyy-MM-dd HH:mm:ss.SSS のようなフォーマット)に指定した数の月を追加した日付を返します。 |
|
MONTHS_BETWEEN(start_date_expr, end_date_expr) |
整数が返されるのは、両方の日付の日の部分が同じ場合、または両方が月の最終日の場合です。それ以外の場合は、1か月を31日として差が計算されます。 |
|
FROM_UTC_TIMESTAMP(time_stamp, time_zone) |
日付、タイムスタンプまたは文字列をUTC時間として解釈し、その時間を指定したタイムゾーンのタイムスタンプに変換します。 文字列の場合は、 タイム・ゾーンのフォーマットは、リージョンベースのゾーンID(例: 'Asia/Seoul'のような'area/city')またはタイム・ゾーン・オフセット(例: UTC+02)のいずれかです。 |
FROM_UTC_TIMESTAMP('2017-07-14 02:40:00.0', 'GMT+1') は2017-07-14 03:40:00.0 を返します |
TO_UTC_TIMESTAMP(time_stamp, time_zone) |
指定したタイムゾーンの日付、タイムスタンプまたは文字列をUTCタイムスタンプに変換します。 文字列の場合は、 タイム・ゾーンのフォーマットは、リージョンベースのゾーンID(例: 'Asia/Seoul'のような'area/city')またはタイム・ゾーン・オフセット(例: UTC+02)のいずれかです。 |
TO_UTC_TIMESTAMP('2017-07-14 02:40:00.0', 'GMT+1') は2017-07-14 01:40:00.0 を返します
|
FROM_UNIXTIME(unix_time[, fmt]) |
指定したUnix時間すなわちエポックを、現在のシステム・タイム・ゾーンおよび指定したフォーマットで、その時点のタイムスタンプを表す文字列に変換します。 ノート: Unix timeは、1970年1月1日00:00:00 UTCから経過した秒数です。
|
例のデフォルトのタイムゾーンはPSTです |
UNIX_TIMESTAMP([time_expr[, fmt]]) |
現在時刻または指定した時刻をUnixタイムスタンプ(秒単位)に変換します。
|
この例のデフォルトのタイムゾーンはPSTです |
INTERVAL 'year' YEAR[(year_precision)] |
期間を年単位で返します。 year_precisionはyearフィールドの桁数で、範囲は0から9です。year_precisionを省略した場合、デフォルトは2です(100年未満である必要があります)。 |
|
INTERVAL 'year month' YEAR[(year_precision)] TO MONTH |
期間を年と月の単位で返します。yearおよびmonthフィールドを使用して期間を格納するために使用します。 year_precisionはyearフィールドの桁数で、範囲は0から9です。year_precisionを省略した場合、デフォルトは2です(100年未満である必要があります)。 |
INTERVAL '100-5' YEAR(3) TO MONTH は100年と5か月の期間を返します。先頭の年の精度として3を指定する必要があります。 |
INTERVAL 'month' MONTH[(month_precision)] |
期間を月単位で返します。 month_precisionはmonthフィールドの桁数で、範囲は0から9です。month_precisionを省略した場合、デフォルトは2です(100年未満である必要があります)。 |
INTERVAL '200' MONTH(3) は200か月の期間を返します。月の精度として3を指定する必要があります。 |
INTERVAL 'day time' DAY[(day_precision)] TO SECOND[(fractional_seconds_precision)] |
日、時、分、秒で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 fractional_seconds_precisionは、timeフィールドの秒の値の分数部分の桁数であり、範囲は0から9です。 |
INTERVAL '11 10:09:08.555' DAY TO SECOND(3) は、11日10時間9分8秒555ミリ秒の期間を返します |
INTERVAL 'day time' DAY[(day_precision)] TO MINUTE[(minute_precision)] |
日、時、分で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '11 10:09' DAY TO MINUTE は、11日10時間9分の期間を返します |
INTERVAL 'day time' DAY[(day_precision)] TO HOUR[(hour_precision)] |
日数と時間数で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '100 10' DAY(3) TO HOUR は、100日10時間の期間を返します |
INTERVAL 'day' DAY[(day_precision)] |
日数で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 |
INTERVAL '999' DAY(3) は999日の期間を返します |
INTERVAL 'time' HOUR[(hour_precision)] TO SECOND[(fractional_seconds_precision)] |
時、分、秒で期間を返します。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 fractional_seconds_precisionは、timeフィールドの秒の値の分数部分の桁数であり、範囲は0から9です。 |
INTERVAL '09:08:07.6666666' HOUR TO SECOND(7) は、9時間8分7.6666666秒の期間を返します |
INTERVAL 'time' HOUR[(hour_precision)] TO MINUTE[(minute_precision)] |
時間と分数で期間を返します。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '09:30' HOUR TO MINUTE は、9時間30分の期間を返します |
INTERVAL 'hour' HOUR[(hour_precision)] |
時間数で期間を返します。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '40' HOUR は40時間の期間を返します |
INTERVAL 'minute' MINUTE[(minute_precision)] |
分数で期間を返します。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '15' MINUTE は15分間の期間を返します |
INTERVAL 'time' MINUTE[(minute_precision)] TO SECOND[(fractional_seconds_precision)] |
分数と秒数で期間を返します。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 fractional_seconds_precisionは、timeフィールドの秒の値の分数部分の桁数であり、範囲は0から9です。 |
INTERVAL '15:30' MINUTE TO SECOND は15分30秒の期間を返します |
INTERVAL 'second' SECOND[(fractional_seconds_precision)] |
秒数で期間を返します。 fractional_seconds_precisionは、secondフィールドの分数部分の桁数であり、範囲は0から9です。デフォルトは3です。 |
INTERVAL '15.678' SECOND は15.678秒の期間を返します |
関数 | 説明 | 例 |
---|---|---|
FIRST_VALUE(value) OVER ([ partition_clause ] order_by_clause [ windowFrame_clause ] ) |
ウィンドウ・フレームの最初の行である行で評価された値を返します。 | FIRST_VALUE(BANK_ID) OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) は、現在行とその行の後の1行の間にある行を計算し、BANK_ID でパーティション化し、BANK_NAME で昇順にしたときの、ウィンドウ内のBANK_ID の最初の値を返します。 |
LAG(value[, offset[, default]]) OVER ([ partition_clause ] order_by_clause) |
パーティション内の現在行より前の指定されたオフセットで、行で評価された値を返します。そのような行がない場合は、デフォルト値が返されます。現在の行に基づいて、オフセットとデフォルトの両方が評価されます。省略した場合、オフセットは1に設定され、デフォルトはNULLに設定されます。 | LAG(BANK_ID, 2, 'hello') OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME DESC) は、BANK_ID でパーティション化し、BANK_NAME の降順にしたときの、現在行より2行前のBANK_ID の値を返します。そのような値がない場合は、hello が返されます。 |
LAST_VALUE(value) OVER ([ partition_clause ] order_by_clause [ windowFrame_clause ]) |
ウィンドウ・フレームの最後の行である行で評価された値を返します。 | LAST_VALUE(BANK_ID) OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) は、現在行とその行の後の1行の間にある行を計算し、BANK_ID でパーティション化し、BANK_NAME で昇順にしたときの、ウィンドウ内のBANK_ID の最後の値を返します。 |
LEAD(value[, offset[, default]]) OVER ([ partition_clause ] order_by_clause) |
パーティション内の現在行より後の特定のオフセットにある行で評価された値を返します。そのような行がない場合は、デフォルト値が返されます。現在の行に基づいて、オフセットとデフォルトの両方が評価されます。省略した場合、オフセットは1に設定され、デフォルトはNULLに設定されます。 | LEAD(BANK_ID, 2, 'hello') OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME) は、BANK_ID でパーティション化し、BANK_NAME の昇順にしたときの、現在行より2行後のBANK_ID の値を返します。そのような値がない場合は、hello が返されます。 |
RANK() OVER([ partition_clause ] order_by_clause) |
ギャップを含む現在行のランクを返します(1からカウント)。 | RANK() OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME) は、BANK_ID のパーティション・グループ内の各行のランクをBANK_NAME の昇順で返します。 |
ROW_NUMBER() OVER([ partition_clause ] order_by_clause) |
パーティション内の現在行の一意の番号を返します(1からカウント)。 | ROW_NUMBER() OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME) は、BANK_ID のパーティション・グループ内の各行の一意の行番号をBANK_NAME の昇順で返します。 |
関数 | 説明 | 例 |
---|---|---|
CAST(value AS type) | 指定されたタイプの指定された値を返します。 | CAST("10" AS INT) は10 を返します |
CONCAT(string, string) | 文字列または列を結合した値を返します | CONCAT('Oracle','SQL') はOracleSQL を返します |
CONCAT_WS(separator, expression1, expression2, expression3,...) |
指定したセパレータを使用して、文字列または列を結合した値を文字列または列間で返します。 区切り記号は必須であり、文字列である必要があります。 セパレータの後に少なくとも1つの式を指定する必要があります。たとえば: |
CONCAT_WS('-', 'Hello', 'Oracle') はHello-Oracle を返します
関数の子が配列である場合、配列はフラット化されます。
|
INITCAP(string) | 各単語の最初の文字を大文字、残りの文字をすべて小文字にした文字列を、単語どうしを空白で区切って返します。 | INITCAP('oRACLE sql') はOracle Sql を返します |
INSTR(string, substring[start_position]) | string で最初に出現するsubstring の(1から始まる)索引を返します | INSTR('OracleSQL', 'SQL') は7 を返します |
LOWER(string) | すべての文字を小文字に変更して文字列を返します。 | LOWER('ORACLE') はoracle を返します |
LENGTH(string) | 文字列の文字長またはバイナリ・データのバイト数を返します。文字列の長さには末尾のスペースも含まれます。 | LENGTH('Oracle') は6 を返します |
LTRIM(string) | 先頭のスペースを左から除去して文字列を返します。 | LTRIM(' Oracle') |
NVL(expr1, epxr2) | nullでない引数を返します。 | NVL(EXPRESSION_3.CUSTOMERS_JSON.CONTINENT_ID, ROWID()) |
REGEXP_SUBSTR(string, regexp[, RegexGroupIdx]) | 入力文字列から正規表現パターンに一致する文字列を検索して抽出します。オプションのキャプチャ・グループ索引が指定されている場合、この関数は特定のグループを抽出します。 |
REGEXP_SUBSTR('22;33;44', '([0-9.]*);([0-9.]*);([0-9.]*)', 1) は22 を返します |
REPLACE(string, search, replacement) | 出現するすべてのsearch をreplacement に置換します。文字列に
| REPLACE('ABCabc', 'abc', 'DEF') はABCDEF を返します |
RTRIM(string) | 先頭のスペースを右から除去して文字列を返します。 | RTRIM('Oracle ') |
SUBSTRING(string, position[, substring_length]) | 位置から始まる部分文字列を返します。 | SUBSTRING('Oracle SQL' FROM 2 FOR 3) はrac を返します |
数値の場合、TO_CHAR(expr) および日付の場合、TO_CHAR(expr, format[, locale]) | 数字と日付を文字列に変換します。数値の場合、書式は必要ありません。日付の場合は、「日時関数」で説明されているDATE_FORMAT と同じ形式を使用します。デフォルトのロケールはen-US です。「サポートされている言語タグ」を参照してください。パイプライン式では、
| 数値の例: 日付の例: |
UPPER(string) | すべての文字を大文字に変更して文字列を返します。 | UPPER('oracle') はORACLE を返します |
LPAD(str, len[, pad]) | 文字列の左側に指定した文字を特定の長さまで埋め込んで返します。pad文字を省略した場合、デフォルトは空白です。 | LPAD('ABC', 5, '*') は'**ABC' を返します |
RPAD(str, len[, pad]) | 文字列の右側に指定した文字を特定の長さまで埋め込んで返します。pad文字を省略した場合、デフォルトは空白です。 | RPAD('XYZ', 6, '+' )は'XYZ+++' を返します |
関数 | 説明 | 例 |
---|---|---|
CASE WHEN condition1 THEN result1 ELSE result2 END | 条件を満たす値を返します。 | CASE WHEN 1 > 0 THEN 'ABC' ELSE 'XYZ' END は、1> 0 の場合はABC を返し、それ以外の場合はXYZ を返します |
AND | 論理AND演算子。両方のオペランドがtrueの場合はtrueを返し、それ以外の場合はfalseを返します。 | (x = 10 AND y = 20)は、xが10でyが20の場合、trueを返します。いずれか一方がtrueでない場合は、falseを返します |
OR | 論理OR演算子。いずれかのオペランドがtrueであるか、両方ともtrueである場合はtrueを返し、それ以外の場合はfalseを返します。 | (x = 10 OR y = 20)は、xが10ではなく、かつyが20でない場合、falseを返します。いずれか一方がtrueの場合は、trueを返します |
NOT | 論理NOT演算子。 | |
LIKE | string1がstring2のパターンに一致するかどうかにかかわらず、文字列パターン・マッチングを実行します。 | |
= | 等価かどうかをテストします。expr1がexpr2と等しい場合はtrueを返し、それ以外の場合はfalseを返します。 | x = 10は、xの値が10の場合はtrueを返し、それ以外の場合はfalseを返します |
!= | 非等価かどうかをテストします。expr1がexpr2と等しくない場合はtrueを返し、それ以外の場合はfalseを返します。 | x != 10は、xの値が10の場合はfalseを返し、それ以外の場合はtrueを返します |
> | 式の大なりをテストします。expr1がexpr2より大きい場合は、trueを返します。 | x > 10は、xの値が10より大きい場合はtrueを返し、それ以外の場合はfalseを返します |
>= | 式の大なりイコールをテストします。expr1がexpr2以上の場合は、trueを返します。 | x > =10は、xの値が10以上の場合はtrueを返し、それ以外の場合はfalseを返します |
< | 式の小なりをテストします。expr1がexpr2より小さい場合は、trueを返します。 | x < 10は、xの値が10より小さい場合はtrueを返し、それ以外の場合はfalseを返します |
<= | 式の小なりイコールをテストします。expr1がexpr2以下の場合は、trueを返します。 | x <= 10は、xの値が10より小さい場合はtrueを返し、それ以外の場合はfalseを返します |
|| | 2つの文字列を連結します。 | 'XYZ' || 'hello' は'XYZhello' を返します |
BETWEEN | 範囲を評価します。 | FILTER_1.BANK.BANK_ID BETWEEN 1003 AND 1007 |
IN | 式が値リストと一致するかどうかをテストします。 | FILTER_2.ORDERS.ORDER_ID IN (1003, 1007) |
関数 | 説明 | 例 |
---|---|---|
NUMERIC_ID() | 各行に対して64ビットの数値の汎用一意識別子を生成します。 | NUMERIC_ID() は、たとえば3458761969522180096 および3458762008176885761 を返します |
ROWID() | 単調に増加する64ビット数を生成します。 | ROWID() は、たとえば、0 、1 、2 などを返します |
UUID() | 各行に対して128ビットの文字列の汎用一意識別子を生成します。 | UUID() は、たとえば20d45c2f-0d56-4356-8910-162f4f40fb6d を返します |
MONOTONICALLY_INCREASING_ID() |
単調に増加する一意の64ビット整数を生成します。連続した数字にはなりません。 | MONOTONICALLY_INCREASING_ID() は、たとえば、8589934592 や25769803776 を返します |
関数 | 説明 | 例 |
---|---|---|
COALESCE(value, value [, value]*) | 存在する場合は最初のnullでない引数を返し、それ以外の場合はnullを返します。 | COALESCE(NULL, 1, NULL) は1 を返します |
NULLIF(value, value) | 2つの値が等しい場合はnullを返し、それ以外の場合は最初の値を返します。 | NULLIF('ABC','XYZ') はABC を返します |
関数 | 説明 | 例 |
---|---|---|
SCHEMA_OF_JSON(string) | JSON文字列を解析し、スキーマのDDLフォーマットを推測します。 |
|
FROM_JSON(column, string) | JSON文字列を含む列を解析し、指定スキーマを含む次のいずれかの型に解析します。
|
|
TO_JSON(column) | StructまたはStruct配列あるいはMapまたはMap配列の型を含む列をJSON文字列に変換します。 | TO_JSON(TO_STRUCT('s1', TO_ARRAY(1,2,3), 's2', TO_MAP('key', 'value'))) はJSON文字列{"s1":[1,2,3],"s2":{"key":"value"}} を返します |
TO_MAP(string,column[,string,column]*) | Map型の新しい列を作成します。入力列は、キーと値のペアとしてグループ化する必要があります。入力キー列をNULLにすることはできません。すべて同じデータ型である必要があります。入力値列はすべて同じデータ型である必要があります。 |
|
TO_STRUCT(string,column[,string,column]*) | Struct型の新しい列を作成します。入力列は、キーと値のペアとしてグループ化する必要があります。 |
|
TO_ARRAY(column[,column]*) | Array型の新しい列を作成します。入力列はすべて同じデータ型である必要があります。 |
|
式および階層データ型の作成をサポートするデータ・フロー演算子では、上位の関数を使用できます。
サポートされている演算子は次のとおりです:
-
集計
-
式
-
フィルタ
-
結合
-
検索
-
分割済
-
ピボット
関数 | 説明 | 例 |
---|---|---|
TRANSFORM(column, lambda_function) | 配列と無名関数を取得し、各要素に関数を適用して結果を出力配列に割り当てることによって、新しい配列を設定します。 | 整数[1, 2, 3] の入力配列の場合、TRANSFORM(array, x -> x + 1) は[2, 3, 4] の新しい配列を返します。 |
TRANSFORM_KEYS(column, lambda_function) | 2つの引数(キーと値)を持つマップと関数を取得し、キーがラムダ関数の結果のタイプを持ち、値が列マップ値のタイプを持つマップを返します。 | 整数キーおよび文字列値が{1 -> 'value1', 2 -> 'value2', 3 -> 'value3'} の入力マップの場合、TRANSFORM_KEYS(map, (k, v) -> k * 2 + 1) は{3 -> 'value1', 5 -> 'value2', 7 -> 'value3'} の新しいマップを返します。 |
TRANSFORM_VALUES(column, lambda_function) | 2つの引数(キーと値)を持つマップと関数を取得し、値がラムダ関数の結果のタイプを持ち、キーが列マップ・キーのタイプを持つマップを返します。 | 文字列キーおよび文字列値が{'a' -> 'value1', 'b' -> 'value2', 'c' -> 'value3'} の入力マップの場合、TRANSFORM_VALUES(map, (k, v) -> k || __ '' || v) は{'a' -> 'a_value1', 'b' -> 'b_value2', 'c' -> 'c_value3'} の新しいマップを返します。 |
ARRAY_SORT(array(...), lambda_function) |
式演算子のみが 配列を取得し、2つの引数を取る指定された関数に従ってソートします。 この関数は、最初のエレメントが2番目のエレメントより小さいか、等しいか、または大きいかに応じて-1、0、または1を返す必要があります。 関数を省略すると、配列は昇順にソートされます。 |
返される配列は次のとおりです。 [1,5,6] |
式演算子に追加された式は複製できます。
式演算子に追加された式の順序は変更できます。
集計演算子
集計演算子を使用して、すべての行または行のグループに対して合計や件数などの計算を実行することにより、新しい派生属性を作成します。
式ビルダーを使用して要素を視覚的に選択し、エディタで式を作成します。式を自ら手動で記述することもできます。
「式の追加」パネルには、2つのセクション(「式情報」と「式ビルダー」)があります。「式情報」フィールドでは、式の名前とデータ型を指定できます。複数の属性に適用する式を作成することもできます。ネストされた型のレベルが含まれる、Map、Array、Structなどの複合データ型を扱う際には、入力した式からビルダーがデータ型を検出するように選択できます。データ型をビルダーで推測するときは、データ型をプレビューおよびリフレッシュし、式を検証できます。
「式ビルダー」セクションには、式を作成するための要素がリストされます。式で使用できる要素には、受信属性、パラメータおよび関数があります。リストの要素をダブルクリックするかリストからエディタにドラッグして式を作成するか、自分で式を記述します。式は作成する前に検証できます。
「受信」には、アップストリーム演算子からこの式演算子に入る属性が表示されます。属性リストの下には、除外ルールを適用するためのチェック・ボックスがあります。この式の出力から1つ以上の受信属性を除外するには、「受信属性の除外」チェック・ボックスを選択します。次に、メニューを使用して、出力から除外する受信属性を追加します。除外ルールが、除外対象として選択した各属性に適用されます。属性を除外できるのは、式を初めて追加するときだけです。式を編集する場合、「受信属性の除外」チェック・ボックスは使用できません。
パラメータは、条件ビルダー(フィルタ演算子、結合演算子、ルックアップ演算子および分割演算子)または式ビルダー(式演算子および集計演算子)を使用してデータ・フローに追加された式パラメータです。式パラメータには、名前、タイプおよびデフォルト値があります。式パラメータの追加を参照してください。
ファンクションは、関数に渡される引数に対して実行される操作です。ファンクションは、引数からデータ値を計算、操作または抽出します。式の作成に使用できる関数のリストを次に示します:
関数 | 説明 | 例 |
---|---|---|
COUNT(value[, value]*) | 指定された1つ以上の式がすべてnullでない行の数を返します。 | COUNT(expr1) |
COUNT(*) | 取得された行の合計数(nullのある行を含む)を返します。 | COUNT(*) |
MAX(value) | 引数の最大値を返します。 | MAX(expr) |
MIN(value) | 引数の最小値を返します。 | MIN(expr) |
SUM(numeric) | グループの値から計算された合計を返します。 | SUM(expr1) |
AVG(numeric) | 式の数値の平均を返します。 | AVG(AGGREGATE_1.src1.attribute1) |
LISTAGG(column[, delimiter]) WITHIN GROUP (order_by_clause) | order句に基づいてグループごとに、指定したデリミタで入力列の値を連結します。 columnには、結果で連結する値が含まれます。 delimiterは、結果でcolumn値を区切ります。delimiterを指定しないと、空白文字が使用されます。 order_by_clauseは、連結した値を返す順序を決定します。 この関数はアグリゲータとしてのみ使用できます。また、グループ化の有無に関係なく使用できます。グループ化せずに使用すると、結果が単一行になります。グループ化して使用すると、各グループに1行ずつ返されます。 |
例1: グループ化なし
例2:
|
式および階層データ型の作成をサポートするデータ・フロー演算子では、上位の関数を使用できます。
サポートされている演算子は次のとおりです:
-
集計
-
式
-
フィルタ
-
結合
-
検索
-
分割済
-
ピボット
関数 | 説明 | 例 |
---|---|---|
TRANSFORM(column, lambda_function) | 配列と無名関数を取得し、各要素に関数を適用して結果を出力配列に割り当てることによって、新しい配列を設定します。 | 整数[1, 2, 3] の入力配列の場合、TRANSFORM(array, x -> x + 1) は[2, 3, 4] の新しい配列を返します。 |
TRANSFORM_KEYS(column, lambda_function) | 2つの引数(キーと値)を持つマップと関数を取得し、キーがラムダ関数の結果のタイプを持ち、値が列マップ値のタイプを持つマップを返します。 | 整数キーおよび文字列値が{1 -> 'value1', 2 -> 'value2', 3 -> 'value3'} の入力マップの場合、TRANSFORM_KEYS(map, (k, v) -> k * 2 + 1) は{3 -> 'value1', 5 -> 'value2', 7 -> 'value3'} の新しいマップを返します。 |
TRANSFORM_VALUES(column, lambda_function) | 2つの引数(キーと値)を持つマップと関数を取得し、値がラムダ関数の結果のタイプを持ち、キーが列マップ・キーのタイプを持つマップを返します。 | 文字列キーおよび文字列値が{'a' -> 'value1', 'b' -> 'value2', 'c' -> 'value3'} の入力マップの場合、TRANSFORM_VALUES(map, (k, v) -> k || __ '' || v) は{'a' -> 'a_value1', 'b' -> 'b_value2', 'c' -> 'c_value3'} の新しいマップを返します。 |
ARRAY_SORT(array(...), lambda_function) |
式演算子のみが 配列を取得し、2つの引数を取る指定された関数に従ってソートします。 この関数は、最初のエレメントが2番目のエレメントより小さいか、等しいか、または大きいかに応じて-1、0、または1を返す必要があります。 関数を省略すると、配列は昇順にソートされます。 |
返される配列は次のとおりです。 [1,5,6] |
個別演算子
個別演算子を使用して、一意の値を持つ個別行を返します。
ソート演算子
ソート演算子を使用して、昇順または降順でのデータのソートを実行します。
ソート演算子を使用する場合は、他の演算子の適用後にソート演算子を適用することをお薦めします。これにより、ソート演算子がターゲット演算子の直前に配置されるため、データを特定の順序でターゲットに挿入できます。
ソート演算子を使用する場合は、他の整形演算子より後で、データのソートが必要な演算子の前に、ソート演算子を適用します。
たとえば、データを特定のソート順でターゲットに挿入するには、ターゲット演算子の前にソート演算子を適用します。
キャンバスでソート演算子を追加し、それを別の演算子と接続した後、ソート条件を追加できます。
「ソート条件の追加」パネルで、リストされたフィールドからソートする属性を選択したり、パターンを使用して属性名をフィルタできます。文字列データ型の場合、ソート操作は辞書式順序に基づいて行われます。
ソート条件を追加するには:
- 「詳細」タブで、「ソート条件」セクションに移動し、「追加」をクリックします。
「ソート条件の追加」パネルには、ソース表からフェッチされたすべての属性フィールドが表示されます。
- 「ソート条件の追加」パネルで、データのソート基準となる属性を選択します。
- 名前パターンを使用して属性をフィルタするには、名前パターン(*CITY*など)を入力します。
- 「ソート順」で、「昇順」または「降順」を選択して、「追加」をクリックします。
追加した各条件が、ソート条件リストに表示されます。
ノート
複数のソート条件は1つずつ追加できます。リスト内のソート条件の順序に基づいてソートが実行されます。たとえば、まず、リスト内の最初の条件に基づいてソートが実行されてから、ソートされたデータが2番目の条件に基づいて再ソートされます。
リスト内でソート条件を上下に移動して、ソートの優先順位を設定します。
並べ替え条件を上下に移動すると、まず優先度の高い並べ替え条件でソートしてから、並べ替えたデータをリスト内の次の条件で再ソートできます。たとえば、まず住所でソートしてから郵便番号でソートするには、住所を含むソート条件を最上位に移動します。
ソート条件に優先順位を付けるには:
ソート条件を編集するには:
ソート条件は、個別に削除することも、一括削除することもできます。
ソート条件を削除するには:
- 「詳細」タブで、「ソート条件」セクションに移動します。
-
ソート条件を1つずつ削除するには、削除するソート条件に対応する「アクション」メニュー(
)をクリックします。
- 複数のソート条件を削除するには、それぞれに対応するチェック・ボックスを選択して、リスト上部にある「削除」をクリックします。
和集合演算子
ユニオン演算子を使用して、複数のデータセット間のユニオン操作を実行します。
和集合操作は、最大10個のソース演算子に対して実行できます。少なくとも2つのソース入力を構成する必要があります。ソース入力属性全体で属性名を照合して和集合操作を実行するように選択することも、属性の位置で属性を照合することもできます。
次の2つのデータ・エンティティの例について考えてみます。データ・エンティティ1がプライマリ入力として設定されています。結果のデータ・エンティティは、名前による和集合操作中に2つの入力からのデータをどのように結合するかを示します。結果のデータ・エンティティは、プライマリ入力データ・エンティティからの属性名、順序およびデータ型を使用します。
データ・エンティティ1、プライマリ入力
部門 | 事業所ID | 倉庫 |
---|---|---|
IT | 1400 | サンフランシスコ |
出荷 | 1500 | サウスレイク(テキサス州) |
経理 | 1700 | ニュージャージー |
データ・エンティティ2
倉庫 | 事業所ID | 部門 |
---|---|---|
デンバー | 1600 | 福利厚生 |
ニューヨーク | 1400 | 建設 |
結果のデータ・エンティティ
部門 | 事業所ID | 倉庫 |
---|---|---|
IT | 1400 | サンフランシスコ |
出荷 | 1500 | サウスレイク(テキサス州) |
経理 | 1700 | ニュージャージー |
福利厚生 | 1600 | デンバー |
建設 | 1400 | ニューヨーク |
次の2つのデータ・エンティティの例について考えてみます。データ・エンティティ2がプライマリ入力として設定されています。結果のデータ・エンティティは、位置による和集合操作中に2つの入力からのデータをどのように結合するかを示します。結果のデータ・エンティティは、プライマリ入力データ・エンティティからの属性名、順序およびデータ型を使用します。
データ・エンティティ1
部門 | 事業所ID | 倉庫 |
---|---|---|
IT | 1400 | サンフランシスコ |
出荷 | 1500 | サウスレイク(テキサス州) |
経理 | 1700 | ニュージャージー |
データ・エンティティ2、プライマリ入力
倉庫 | 事業所ID | 部門 |
---|---|---|
デンバー | 1600 | 福利厚生 |
ニューヨーク | 1400 | 建設 |
結果のデータ・エンティティ
倉庫 | 事業所ID | 部門 |
---|---|---|
デンバー | 1600 | 福利厚生 |
ニューヨーク | 1400 | 建設 |
IT | 1400 | サンフランシスコ |
出荷 | 1500 | サウスレイク(テキサス州) |
経理 | 1700 | ニュージャージー |
マイナス演算子
マイナス演算子を使用して、2つのデータ・エンティティを比較し、一方のエンティティには存在するがもう一方のエンティティには存在しない行を返します。
結果のデータで重複する行を保持するか削除するかを選択できます。
マイナス操作は、2つのソース演算子に対してのみ実行できます。ソース入力属性全体で属性名を照合してマイナス操作を実行するように選択することも、属性の位置で属性を照合することもできます。
次の2つのデータ・エンティティの例について考えてみます。データ・エンティティ1がプライマリ入力として設定されています。結果のデータ・エンティティは、名前によるマイナス操作中に2つの入力からのデータをどのように減算するかを示します。結果のデータ・エンティティは、プライマリ入力データ・エンティティからの属性名、順序およびデータ型を使用します。
データ・エンティティ1、プライマリ入力
部門 | 事業所ID | 倉庫 |
---|---|---|
IT | 1400 | サンフランシスコ |
出荷 | 1500 | オースティン |
経理 | 1700 | ニュージャージー |
データ・エンティティ2
部門 | 倉庫 | 事業所ID |
---|---|---|
福利厚生 | デンバー | 1600 |
IT | サンフランシスコ | 1400 |
結果のデータ・エンティティ
部門 | 事業所ID | 倉庫 |
---|---|---|
出荷 | 1500 | オースティン |
経理 | 1700 | ニュージャージー |
次の2つのデータ・エンティティの例について考えてみます。データ・エンティティ2がプライマリ入力として設定されています。結果のデータ・エンティティは、位置によるマイナス操作中に2つの入力からのデータをどのように減算するかを示します。結果のデータ・エンティティは、プライマリ入力データ・エンティティからの属性名、順序およびデータ型を使用します。
データ・エンティティ1
部門 | 事業所ID | 倉庫 |
---|---|---|
IT | 1400 | サンフランシスコ |
出荷 | 1500 | オースティン |
経理 | 1700 | ニュージャージー |
データ・エンティティ2、プライマリ入力
部門名 | 事業所 | 倉庫の市区町村 |
---|---|---|
福利厚生 | 1600 | デンバー |
IT | 1400 | サンフランシスコ |
結果のデータ・エンティティ
部門名 | 事業所 | 倉庫の市区町村 |
---|---|---|
福利厚生 | 1600 | デンバー |
論理積演算子
論理積演算子を使用して、複数のデータ・エンティティを比較し、接続されたエンティティに存在する行を返します。
結果のデータで重複する行を保持するか削除するかを選択できます。
論理積操作は、2つ以上のソース演算子に対して実行できます。ソース入力属性全体で属性名を照合して操作を実行するように選択することも、属性の位置で属性を照合することもできます。
次の2つのデータ・エンティティの例について考えてみます。データ・エンティティ1がプライマリ入力として設定されています。結果のデータ・エンティティは、2つの入力からのデータを属性名でどのように交差するかを示します。結果のデータ・エンティティは、プライマリ入力データ・エンティティからの属性名、順序およびデータ型を使用します。
データ・エンティティ1、プライマリ入力
部門 | 事業所ID | 倉庫 |
---|---|---|
IT | 1400 | サンフランシスコ |
出荷 | 1500 | オースティン |
経理 | 1700 | ニュージャージー |
データ・エンティティ2
部門 | 倉庫 | 事業所ID |
---|---|---|
福利厚生 | デンバー | 1600 |
IT | サンフランシスコ | 1400 |
結果のデータ・エンティティ
部門 | 事業所ID | 倉庫 |
---|---|---|
IT | 1400 | サンフランシスコ |
次の2つのデータ・エンティティの例について考えてみます。データ・エンティティ2がプライマリ入力として設定されています。結果のデータ・エンティティは、2つの入力からのデータを属性位置でどのように交差するかを示します。結果のデータ・エンティティは、プライマリ入力データ・エンティティからの属性名、順序およびデータ型を使用します。
データ・エンティティ1
部門 | 事業所ID | 倉庫 |
---|---|---|
IT | 1400 | サンフランシスコ |
出荷 | 1500 | オースティン |
経理 | 1700 | ニュージャージー |
データ・エンティティ2、プライマリ入力
部門名 | 事業所 | 倉庫の市区町村 |
---|---|---|
福利厚生 | 1600 | デンバー |
IT | 1400 | サンフランシスコ |
結果のデータ・エンティティ
部門名 | 事業所 | 倉庫の市区町村 |
---|---|---|
IT | 1400 | サンフランシスコ |
分割演算子
分割演算子を使用して、シーケンス内で評価される分割条件に基づいて、入力データの1つのソースを複数の出力ポートに分割します。
各分割条件には出力ポートがあります。条件を満たすデータは、対応する出力ポートに転送されます。
デフォルトでは、分割演算子は不一致条件で構成されます。これは常に、シーケンス内の最後の条件として使用可能です。不一致条件に独自の条件を追加することはできません。また、不一致条件を削除することもできません。
演算子は、条件を1つずつ評価します。シーケンス内のすべての条件が評価された後、条件を満たさないデータは不一致出力ポートに転送されます。
属性BANK_IDおよびBANK_NAMEを持つデータ・エンティティBANKがあるとします。
2つの分割条件を設定します。不一致条件を含む完全なシーケンスは次のとおりです:
条件出力ポート | 条件 |
---|---|
CONDITION1 | SPLIT_1.BANK.BANK_ID<102 |
CONDITION2 | SPLIT_1.BANK.BANK_ID<104 |
UNMATCHED | デフォルトのUNMATCHED条件は、シーケンス内の他の条件を満たしていないすべてのデータをUNMATCHED出力ポートに転送します |
BANKデータ・エンティティ
データ・エンティティには4つの行があります。
BANK_ID | BANK_NAME |
---|---|
101 | A Bank 101 |
102 | B Bank 102 |
103 | C Bank 103 |
104 | D Bank 104 |
Condition1の出力、最初の一致条件
CONDITION1は、一致する1行を返します。
BANK_ID | BANK_NAME |
---|---|
101 | A Bank 101 |
Condition2の出力、最初の一致条件
CONDITION2は、(CONDITION1の後の不一致行から)一致する2つの行を返します。
BANK_ID | BANK_NAME |
---|---|
102 | B Bank 102 |
103 | C Bank 103 |
不一致条件の出力、最初の一致条件
UNMATCHED条件は、残りの行を返します。
BANK_ID | BANK_NAME |
---|---|
104 | D Bank 104 |
Condition1の出力、すべての一致条件
CONDITION1は、一致する1行を返します。
BANK_ID | BANK_NAME |
---|---|
101 | A Bank 101 |
Condition2の出力、すべての一致条件
すべてのデータがCONDITION2によって評価され、一致する3つの行が返されます。
BANK_ID | BANK_NAME |
---|---|
101 | A Bank 101 |
102 | B Bank 102 |
103 | C Bank 103 |
不一致条件の出力、すべての一致条件
UNMATCHED条件は、CONDITION1およびCONDITION2を満たさない行を返します。
BANK_ID | BANK_NAME |
---|---|
104 | D Bank 104 |
キャンバスに分割演算子を追加すると、デフォルトで分割演算子アイコンが展開されて表示され、不一致分割条件が示されます。不一致条件は、シーケンスに追加すした他の条件を満たさないすべてのデータを転送します。
条件ビルダーを使用して要素を視覚的に選択し、分割条件を作成および追加します。エディタで分割条件を手動入力することもできます。
既存の条件シーケンスに分割条件を追加できます。分割条件は、シーケンスの最後の不一致条件の前に追加されます。不一致条件に独自の条件を追加することはできません。
分割条件で使用できる要素には、受信属性、パラメータおよび関数があります。要素をダブルクリックするかリストからドラッグしてエディタに追加し、条件を作成できます。条件は作成する前に検証できます。
「受信」には、アップストリーム・ポートからの属性が表示されます。例:
SPLIT_1.BANK.BANK_NAME='ABC Bank'
パラメータは、条件ビルダー(フィルタ演算子、結合演算子、ルックアップ演算子および分割演算子)または式ビルダー(式演算子および集計演算子)を使用してデータ・フローに追加された式パラメータです。式パラメータには、名前、タイプおよびデフォルト値があります。式パラメータの追加を参照してください。
P_VARCHAR_NAME
という名前のVARCHAR
パラメータを作成し、デフォルト値をABC BANK
に設定するとします。この場合、このパラメータを次のように分割条件で使用できます:
SPLIT_1.BANK.BANK_NAME=$P_VARCHAR_NAME
ファンクションはデータ統合で使用可能な関数で、条件の中で使用できます。ファンクションは、関数に渡される引数に対して実行される操作です。ファンクションは、引数からデータ値を計算、操作または抽出します。
P_VARCHAR_LIKE
という名前のVARCHAR
パラメータを作成し、デフォルト値をB%
に設定するとします。この場合、このパラメータを次のように分割条件で使用できます:
SPLIT_1.BANK.BANK_NAME LIKE $P_VARCHAR_LIKE
条件の作成時に追加できる関数のリストを次に示します:
関数 | 説明 | 例 |
---|---|---|
MD5(all data types) | データ型のMD5 チェックサムを計算し、文字列値を返します。 | MD5(column_name) |
SHA1(all data types) | データ型のSHA-1 ハッシュ値を計算し、文字列値を返します。 | SHA1(column_name) |
SHA2(all data types, bitLength) | データ型のSHA-2 ハッシュ値を計算し、文字列値を返します。bitLength は整数です。 | SHA2 (column_name, bitLength can be set to 0 (equivalent to 256), 256, 384, or 512) 。 |
ORA_HASH(expr, [max_bucket], [seed_value]) |
Oracleは、ハッシュ関数を |
|
関数 | 説明 | 例 |
---|---|---|
ABS(numeric) | numeric 値の絶対乗を返します。 | ABS(-1) |
CEIL(numeric) | numeric 値を超えない最小の整数を返します | CEIL(-1,2) |
FLOOR(numeric) | numeric 値を超えない最大の整数を返します。 | FLOOR(-1,2) |
MOD(numeric1, numeric2) | numeric1 をnumeric2 で除算した後の剰余を返します。 | MOD(8,2) |
POWER(numeric1, numeric2) | numeric1 をnumeric2 で累乗します。 | POWER(2,3) |
ROUND(numeric1, numeric2) | numeric1 を小数点以下numeric2 桁に丸めて返します。 | ROUND(2.5,0) |
TRUNC(numeric1, numeric2) | numeric1 を小数点以下numeric2 桁に切り捨てて返します。 | TRUNC(2.5,0) |
TO_NUMBER(expr[, format, locale]) | 指定されたformat およびlocale (オプション)に基づいて、expr を数値に変換します。デフォルトのロケールはen-US です。サポートされる言語タグ。サポートされるフォーマット・パターン:
|
|
関数 | 説明 | 例 |
---|---|---|
CURRENT_DATE |
現在の日付を返します。 | CURRENT_DATE は、2023-05-26 などの今日の日付を返します |
CURRENT_TIMESTAMP |
セッション・タイムゾーンに対する現在の日付と時刻を返します。 | CURRENT_TIMESTAMP は、今日の日付と現在の時刻(2023-05-26 12:34:56 など)を返します
|
DATE_ADD(date, number_of_days) |
指定したdate からnumber 日後の日付を返します。 |
DATE_ADD('2017-07-30', 1) は2017-07-31 を返します |
DATE_FORMAT(expr, format[, locale]) |
指定された サポートされる日付フォーマット・パターン:
|
|
DAYOFMONTH(date) |
特定の日(月間)を返します。 | DAYOFMONTH('2020-12-25') は25 を返します。 |
DAYOFWEEK(date) |
特定の日(週間)を返します。 | DAYOFWEEK('2020-12-25') は、金曜日の6 を返します。米国では、日曜日は1、月曜日は2などとみなされます。 |
DAYOFYEAR(date) |
特定の日(年間)を返します。 | DAYOFYEAR('2020-12-25') は360 を返します |
WEEKOFYEAR(date) |
日付が年内の何番目の週かを返します。 |
|
HOUR(datetime) |
日時の時間の値を返します。 | HOUR('2020-12-25 15:10:30') は15 を返します |
LAST_DAY(date) |
月末の日付を返します。 | LAST_DAY('2020-12-25') は31 を返します |
MINUTE(datetime) |
日時の分の値を返します。 | HOUR('2020-12-25 15:10:30') は10 を返します |
MONTH(date) |
日付の月の値を返します。 | MONTH('2020-06-25') は6 を返します。 |
QUARTER(date) |
日付が属する四半期を返します。 | QUARTER('2020-12-25') は4 を返します。 |
SECOND(datetime) |
日時の秒の値を返します。 | SECOND('2020-12-25 15:10:30') は30 を返します |
TO_DATE(string, format_string[, localeStr]) |
format_string 式を含む文字列式を日付に解析します。ロケールはオプションです。デフォルトはen-US です。サポートされる言語タグ。パイプライン式では、
|
|
TO_TIMESTAMP(expr, format_string[, localeStr]) |
指定されたformat_string およびlocaleStr (オプション)に基づいて、VARCHARのexpr をTIMESTAMPの値に変換します。パイプライン式では、
|
TO_TIMESTAMP('2020-10-11 11:10:10', 'yyyy-MM-dd HH:mm:ss') は、11am 10:10 Oct 11th, 2020 を表すTIMESTAMPオブジェクトを返します |
WEEK(date) |
日付の週の値を返します。 |
WEEK('2020-06-25') は4 を返します |
YEAR(date) |
日付の年の値を返します。 | YEAR('2020-06-25') returns 2020 |
ADD_MONTHS(date_expr, number_months) |
指定した日付、タイムスタンプまたは文字列(yyyy-MM-dd またはyyyy-MM-dd HH:mm:ss.SSS のようなフォーマット)に指定した数の月を追加した日付を返します。 |
|
MONTHS_BETWEEN(start_date_expr, end_date_expr) |
整数が返されるのは、両方の日付の日の部分が同じ場合、または両方が月の最終日の場合です。それ以外の場合は、1か月を31日として差が計算されます。 |
|
FROM_UTC_TIMESTAMP(time_stamp, time_zone) |
日付、タイムスタンプまたは文字列をUTC時間として解釈し、その時間を指定したタイムゾーンのタイムスタンプに変換します。 文字列の場合は、 タイム・ゾーンのフォーマットは、リージョンベースのゾーンID(例: 'Asia/Seoul'のような'area/city')またはタイム・ゾーン・オフセット(例: UTC+02)のいずれかです。 |
FROM_UTC_TIMESTAMP('2017-07-14 02:40:00.0', 'GMT+1') は2017-07-14 03:40:00.0 を返します |
TO_UTC_TIMESTAMP(time_stamp, time_zone) |
指定したタイムゾーンの日付、タイムスタンプまたは文字列をUTCタイムスタンプに変換します。 文字列の場合は、 タイム・ゾーンのフォーマットは、リージョンベースのゾーンID(例: 'Asia/Seoul'のような'area/city')またはタイム・ゾーン・オフセット(例: UTC+02)のいずれかです。 |
TO_UTC_TIMESTAMP('2017-07-14 02:40:00.0', 'GMT+1') は2017-07-14 01:40:00.0 を返します
|
FROM_UNIXTIME(unix_time[, fmt]) |
指定したUnix時間すなわちエポックを、現在のシステム・タイム・ゾーンおよび指定したフォーマットで、その時点のタイムスタンプを表す文字列に変換します。 ノート: Unix timeは、1970年1月1日00:00:00 UTCから経過した秒数です。
|
例のデフォルトのタイムゾーンはPSTです |
UNIX_TIMESTAMP([time_expr[, fmt]]) |
現在時刻または指定した時刻をUnixタイムスタンプ(秒単位)に変換します。
|
この例のデフォルトのタイムゾーンはPSTです |
INTERVAL 'year' YEAR[(year_precision)] |
期間を年単位で返します。 year_precisionはyearフィールドの桁数で、範囲は0から9です。year_precisionを省略した場合、デフォルトは2です(100年未満である必要があります)。 |
|
INTERVAL 'year month' YEAR[(year_precision)] TO MONTH |
期間を年と月の単位で返します。yearおよびmonthフィールドを使用して期間を格納するために使用します。 year_precisionはyearフィールドの桁数で、範囲は0から9です。year_precisionを省略した場合、デフォルトは2です(100年未満である必要があります)。 |
INTERVAL '100-5' YEAR(3) TO MONTH は100年と5か月の期間を返します。先頭の年の精度として3を指定する必要があります。 |
INTERVAL 'month' MONTH[(month_precision)] |
期間を月単位で返します。 month_precisionはmonthフィールドの桁数で、範囲は0から9です。month_precisionを省略した場合、デフォルトは2です(100年未満である必要があります)。 |
INTERVAL '200' MONTH(3) は200か月の期間を返します。月の精度として3を指定する必要があります。 |
INTERVAL 'day time' DAY[(day_precision)] TO SECOND[(fractional_seconds_precision)] |
日、時、分、秒で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 fractional_seconds_precisionは、timeフィールドの秒の値の分数部分の桁数であり、範囲は0から9です。 |
INTERVAL '11 10:09:08.555' DAY TO SECOND(3) は、11日10時間9分8秒555ミリ秒の期間を返します |
INTERVAL 'day time' DAY[(day_precision)] TO MINUTE[(minute_precision)] |
日、時、分で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '11 10:09' DAY TO MINUTE は、11日10時間9分の期間を返します |
INTERVAL 'day time' DAY[(day_precision)] TO HOUR[(hour_precision)] |
日数と時間数で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '100 10' DAY(3) TO HOUR は、100日10時間の期間を返します |
INTERVAL 'day' DAY[(day_precision)] |
日数で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 |
INTERVAL '999' DAY(3) は999日の期間を返します |
INTERVAL 'time' HOUR[(hour_precision)] TO SECOND[(fractional_seconds_precision)] |
時、分、秒で期間を返します。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 fractional_seconds_precisionは、timeフィールドの秒の値の分数部分の桁数であり、範囲は0から9です。 |
INTERVAL '09:08:07.6666666' HOUR TO SECOND(7) は、9時間8分7.6666666秒の期間を返します |
INTERVAL 'time' HOUR[(hour_precision)] TO MINUTE[(minute_precision)] |
時間と分数で期間を返します。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '09:30' HOUR TO MINUTE は、9時間30分の期間を返します |
INTERVAL 'hour' HOUR[(hour_precision)] |
時間数で期間を返します。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '40' HOUR は40時間の期間を返します |
INTERVAL 'minute' MINUTE[(minute_precision)] |
分数で期間を返します。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '15' MINUTE は15分間の期間を返します |
INTERVAL 'time' MINUTE[(minute_precision)] TO SECOND[(fractional_seconds_precision)] |
分数と秒数で期間を返します。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 fractional_seconds_precisionは、timeフィールドの秒の値の分数部分の桁数であり、範囲は0から9です。 |
INTERVAL '15:30' MINUTE TO SECOND は15分30秒の期間を返します |
INTERVAL 'second' SECOND[(fractional_seconds_precision)] |
秒数で期間を返します。 fractional_seconds_precisionは、secondフィールドの分数部分の桁数であり、範囲は0から9です。デフォルトは3です。 |
INTERVAL '15.678' SECOND は15.678秒の期間を返します |
関数 | 説明 | 例 |
---|---|---|
FIRST_VALUE(value) OVER ([ partition_clause ] order_by_clause [ windowFrame_clause ] ) |
ウィンドウ・フレームの最初の行である行で評価された値を返します。 | FIRST_VALUE(BANK_ID) OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) は、現在行とその行の後の1行の間にある行を計算し、BANK_ID でパーティション化し、BANK_NAME で昇順にしたときの、ウィンドウ内のBANK_ID の最初の値を返します。 |
LAG(value[, offset[, default]]) OVER ([ partition_clause ] order_by_clause) |
パーティション内の現在行より前の指定されたオフセットで、行で評価された値を返します。そのような行がない場合は、デフォルト値が返されます。現在の行に基づいて、オフセットとデフォルトの両方が評価されます。省略した場合、オフセットは1に設定され、デフォルトはNULLに設定されます。 | LAG(BANK_ID, 2, 'hello') OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME DESC) は、BANK_ID でパーティション化し、BANK_NAME の降順にしたときの、現在行より2行前のBANK_ID の値を返します。そのような値がない場合は、hello が返されます。 |
LAST_VALUE(value) OVER ([ partition_clause ] order_by_clause [ windowFrame_clause ]) |
ウィンドウ・フレームの最後の行である行で評価された値を返します。 | LAST_VALUE(BANK_ID) OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) は、現在行とその行の後の1行の間にある行を計算し、BANK_ID でパーティション化し、BANK_NAME で昇順にしたときの、ウィンドウ内のBANK_ID の最後の値を返します。 |
LEAD(value[, offset[, default]]) OVER ([ partition_clause ] order_by_clause) |
パーティション内の現在行より後の特定のオフセットにある行で評価された値を返します。そのような行がない場合は、デフォルト値が返されます。現在の行に基づいて、オフセットとデフォルトの両方が評価されます。省略した場合、オフセットは1に設定され、デフォルトはNULLに設定されます。 | LEAD(BANK_ID, 2, 'hello') OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME) は、BANK_ID でパーティション化し、BANK_NAME の昇順にしたときの、現在行より2行後のBANK_ID の値を返します。そのような値がない場合は、hello が返されます。 |
RANK() OVER([ partition_clause ] order_by_clause) |
ギャップを含む現在行のランクを返します(1からカウント)。 | RANK() OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME) は、BANK_ID のパーティション・グループ内の各行のランクをBANK_NAME の昇順で返します。 |
ROW_NUMBER() OVER([ partition_clause ] order_by_clause) |
パーティション内の現在行の一意の番号を返します(1からカウント)。 | ROW_NUMBER() OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME) は、BANK_ID のパーティション・グループ内の各行の一意の行番号をBANK_NAME の昇順で返します。 |
関数 | 説明 | 例 |
---|---|---|
CAST(value AS type) | 指定されたタイプの指定された値を返します。 | CAST("10" AS INT) は10 を返します |
CONCAT(string, string) | 文字列または列を結合した値を返します | CONCAT('Oracle','SQL') はOracleSQL を返します |
CONCAT_WS(separator, expression1, expression2, expression3,...) |
指定したセパレータを使用して、文字列または列を結合した値を文字列または列間で返します。 区切り記号は必須であり、文字列である必要があります。 セパレータの後に少なくとも1つの式を指定する必要があります。たとえば: |
CONCAT_WS('-', 'Hello', 'Oracle') はHello-Oracle を返します
関数の子が配列である場合、配列はフラット化されます。
|
INITCAP(string) | 各単語の最初の文字を大文字、残りの文字をすべて小文字にした文字列を、単語どうしを空白で区切って返します。 | INITCAP('oRACLE sql') はOracle Sql を返します |
INSTR(string, substring[start_position]) | string で最初に出現するsubstring の(1から始まる)索引を返します | INSTR('OracleSQL', 'SQL') は7 を返します |
LOWER(string) | すべての文字を小文字に変更して文字列を返します。 | LOWER('ORACLE') はoracle を返します |
LENGTH(string) | 文字列の文字長またはバイナリ・データのバイト数を返します。文字列の長さには末尾のスペースも含まれます。 | LENGTH('Oracle') は6 を返します |
LTRIM(string) | 先頭のスペースを左から除去して文字列を返します。 | LTRIM(' Oracle') |
NVL(expr1, epxr2) | nullでない引数を返します。 | NVL(EXPRESSION_3.CUSTOMERS_JSON.CONTINENT_ID, ROWID()) |
REGEXP_SUBSTR(string, regexp[, RegexGroupIdx]) | 入力文字列から正規表現パターンに一致する文字列を検索して抽出します。オプションのキャプチャ・グループ索引が指定されている場合、この関数は特定のグループを抽出します。 |
REGEXP_SUBSTR('22;33;44', '([0-9.]*);([0-9.]*);([0-9.]*)', 1) は22 を返します |
REPLACE(string, search, replacement) | 出現するすべてのsearch をreplacement に置換します。文字列に
| REPLACE('ABCabc', 'abc', 'DEF') はABCDEF を返します |
RTRIM(string) | 先頭のスペースを右から除去して文字列を返します。 | RTRIM('Oracle ') |
SUBSTRING(string, position[, substring_length]) | 位置から始まる部分文字列を返します。 | SUBSTRING('Oracle SQL' FROM 2 FOR 3) はrac を返します |
数値の場合、TO_CHAR(expr) および日付の場合、TO_CHAR(expr, format[, locale]) | 数字と日付を文字列に変換します。数値の場合、書式は必要ありません。日付の場合は、「日時関数」で説明されているDATE_FORMAT と同じ形式を使用します。デフォルトのロケールはen-US です。「サポートされている言語タグ」を参照してください。パイプライン式では、
| 数値の例: 日付の例: |
UPPER(string) | すべての文字を大文字に変更して文字列を返します。 | UPPER('oracle') はORACLE を返します |
LPAD(str, len[, pad]) | 文字列の左側に指定した文字を特定の長さまで埋め込んで返します。pad文字を省略した場合、デフォルトは空白です。 | LPAD('ABC', 5, '*') は'**ABC' を返します |
RPAD(str, len[, pad]) | 文字列の右側に指定した文字を特定の長さまで埋め込んで返します。pad文字を省略した場合、デフォルトは空白です。 | RPAD('XYZ', 6, '+' )は'XYZ+++' を返します |
関数 | 説明 | 例 |
---|---|---|
CASE WHEN condition1 THEN result1 ELSE result2 END | 条件を満たす値を返します。 | CASE WHEN 1 > 0 THEN 'ABC' ELSE 'XYZ' END は、1> 0 の場合はABC を返し、それ以外の場合はXYZ を返します |
AND | 論理AND演算子。両方のオペランドがtrueの場合はtrueを返し、それ以外の場合はfalseを返します。 | (x = 10 AND y = 20)は、xが10でyが20の場合、trueを返します。いずれか一方がtrueでない場合は、falseを返します |
OR | 論理OR演算子。いずれかのオペランドがtrueであるか、両方ともtrueである場合はtrueを返し、それ以外の場合はfalseを返します。 | (x = 10 OR y = 20)は、xが10ではなく、かつyが20でない場合、falseを返します。いずれか一方がtrueの場合は、trueを返します |
NOT | 論理NOT演算子。 | |
LIKE | string1がstring2のパターンに一致するかどうかにかかわらず、文字列パターン・マッチングを実行します。 | |
= | 等価かどうかをテストします。expr1がexpr2と等しい場合はtrueを返し、それ以外の場合はfalseを返します。 | x = 10は、xの値が10の場合はtrueを返し、それ以外の場合はfalseを返します |
!= | 非等価かどうかをテストします。expr1がexpr2と等しくない場合はtrueを返し、それ以外の場合はfalseを返します。 | x != 10は、xの値が10の場合はfalseを返し、それ以外の場合はtrueを返します |
> | 式の大なりをテストします。expr1がexpr2より大きい場合は、trueを返します。 | x > 10は、xの値が10より大きい場合はtrueを返し、それ以外の場合はfalseを返します |
>= | 式の大なりイコールをテストします。expr1がexpr2以上の場合は、trueを返します。 | x > =10は、xの値が10以上の場合はtrueを返し、それ以外の場合はfalseを返します |
< | 式の小なりをテストします。expr1がexpr2より小さい場合は、trueを返します。 | x < 10は、xの値が10より小さい場合はtrueを返し、それ以外の場合はfalseを返します |
<= | 式の小なりイコールをテストします。expr1がexpr2以下の場合は、trueを返します。 | x <= 10は、xの値が10より小さい場合はtrueを返し、それ以外の場合はfalseを返します |
|| | 2つの文字列を連結します。 | 'XYZ' || 'hello' は'XYZhello' を返します |
BETWEEN | 範囲を評価します。 | FILTER_1.BANK.BANK_ID BETWEEN 1003 AND 1007 |
IN | 式が値リストと一致するかどうかをテストします。 | FILTER_2.ORDERS.ORDER_ID IN (1003, 1007) |
関数 | 説明 | 例 |
---|---|---|
NUMERIC_ID() | 各行に対して64ビットの数値の汎用一意識別子を生成します。 | NUMERIC_ID() は、たとえば3458761969522180096 および3458762008176885761 を返します |
ROWID() | 単調に増加する64ビット数を生成します。 | ROWID() は、たとえば、0 、1 、2 などを返します |
UUID() | 各行に対して128ビットの文字列の汎用一意識別子を生成します。 | UUID() は、たとえば20d45c2f-0d56-4356-8910-162f4f40fb6d を返します |
MONOTONICALLY_INCREASING_ID() |
単調に増加する一意の64ビット整数を生成します。連続した数字にはなりません。 | MONOTONICALLY_INCREASING_ID() は、たとえば、8589934592 や25769803776 を返します |
関数 | 説明 | 例 |
---|---|---|
COALESCE(value, value [, value]*) | 存在する場合は最初のnullでない引数を返し、それ以外の場合はnullを返します。 | COALESCE(NULL, 1, NULL) は1 を返します |
NULLIF(value, value) | 2つの値が等しい場合はnullを返し、それ以外の場合は最初の値を返します。 | NULLIF('ABC','XYZ') はABC を返します |
関数 | 説明 | 例 |
---|---|---|
SCHEMA_OF_JSON(string) | JSON文字列を解析し、スキーマのDDLフォーマットを推測します。 |
|
FROM_JSON(column, string) | JSON文字列を含む列を解析し、指定スキーマを含む次のいずれかの型に解析します。
|
|
TO_JSON(column) | StructまたはStruct配列あるいはMapまたはMap配列の型を含む列をJSON文字列に変換します。 | TO_JSON(TO_STRUCT('s1', TO_ARRAY(1,2,3), 's2', TO_MAP('key', 'value'))) はJSON文字列{"s1":[1,2,3],"s2":{"key":"value"}} を返します |
TO_MAP(string,column[,string,column]*) | Map型の新しい列を作成します。入力列は、キーと値のペアとしてグループ化する必要があります。入力キー列をNULLにすることはできません。すべて同じデータ型である必要があります。入力値列はすべて同じデータ型である必要があります。 |
|
TO_STRUCT(string,column[,string,column]*) | Struct型の新しい列を作成します。入力列は、キーと値のペアとしてグループ化する必要があります。 |
|
TO_ARRAY(column[,column]*) | Array型の新しい列を作成します。入力列はすべて同じデータ型である必要があります。 |
|
式および階層データ型の作成をサポートするデータ・フロー演算子では、上位の関数を使用できます。
サポートされている演算子は次のとおりです:
-
集計
-
式
-
フィルタ
-
結合
-
検索
-
分割済
-
ピボット
関数 | 説明 | 例 |
---|---|---|
TRANSFORM(column, lambda_function) | 配列と無名関数を取得し、各要素に関数を適用して結果を出力配列に割り当てることによって、新しい配列を設定します。 | 整数[1, 2, 3] の入力配列の場合、TRANSFORM(array, x -> x + 1) は[2, 3, 4] の新しい配列を返します。 |
TRANSFORM_KEYS(column, lambda_function) | 2つの引数(キーと値)を持つマップと関数を取得し、キーがラムダ関数の結果のタイプを持ち、値が列マップ値のタイプを持つマップを返します。 | 整数キーおよび文字列値が{1 -> 'value1', 2 -> 'value2', 3 -> 'value3'} の入力マップの場合、TRANSFORM_KEYS(map, (k, v) -> k * 2 + 1) は{3 -> 'value1', 5 -> 'value2', 7 -> 'value3'} の新しいマップを返します。 |
TRANSFORM_VALUES(column, lambda_function) | 2つの引数(キーと値)を持つマップと関数を取得し、値がラムダ関数の結果のタイプを持ち、キーが列マップ・キーのタイプを持つマップを返します。 | 文字列キーおよび文字列値が{'a' -> 'value1', 'b' -> 'value2', 'c' -> 'value3'} の入力マップの場合、TRANSFORM_VALUES(map, (k, v) -> k || __ '' || v) は{'a' -> 'a_value1', 'b' -> 'b_value2', 'c' -> 'c_value3'} の新しいマップを返します。 |
ARRAY_SORT(array(...), lambda_function) |
式演算子のみが 配列を取得し、2つの引数を取る指定された関数に従ってソートします。 この関数は、最初のエレメントが2番目のエレメントより小さいか、等しいか、または大きいかに応じて-1、0、または1を返す必要があります。 関数を省略すると、配列は昇順にソートされます。 |
返される配列は次のとおりです。 [1,5,6] |
不一致条件以外の分割条件は編集できます。
分割条件は、シーケンス内で上下に移動できます。不一致条件のみ移動できません。
不一致条件以外の分割条件は削除できます。
ピボット演算子
ピボット演算子を使用すると、入力ソースの1つの属性から一意の行値を取得し、その値を出力の複数の属性にピボットできます。
ピボット操作は、複数の行からの入力を使用して、集計関数式およびピボット・キーとして指定した属性の値に基づいて変換を実行します。ピボット操作の結果は、行と属性がピボット(再配置)された出力です。
出力の行数は、グループ化基準として使用する属性の選択に基づきます。
- グループ化基準として1つ以上の属性を指定すると、同じグループ化基準属性値を含む受信行は1つの行にグループ化されます。たとえば、1つのグループ化基準属性を指定し、その属性に一意の値が4つ含まれている場合、受信データが変換されて、出力で4つの行にグループ化されます。
- グループ化基準の属性を指定しない場合、すべての受信データは1つの出力行に変換されます。
出力の属性数は:
- グループ化基準として選択した属性の数に基づきます
- ピボット・キーで選択した値の数の倍数です
- 集計関数式が変換する属性数の結果です
たとえば、1つのグループ化基準属性と3つのピボット・キー値を選択し、2つの属性を変換する式を追加した場合、出力内の属性の数は:
1 + (3 * 2)
結果のピボット済出力内の属性の合計数は、次のように計算されます:
Number of group by attributes + (Number of pivot key values * Number of attributes that are transformed from expressions)
出力内の新規属性の名前は、式の作成時にターゲット属性に追加したパターンから導出されます。
ピボット演算子で使用する集計関数によって、出力のピボット済値が決まります。データが見つからない場合は、ピボット済値が想定される場所にNULLが挿入されます。
STORE、PRODUCTおよびSALESの属性が含まれるデータ・エンティティPRODUCT_SALESについて考えてみます。このデータ・エンティティには5つの行があります。SALESに対する集計SUM関数を使用して、属性PRODUCTに対してピボットを作成するとします。
ピボット済行を属性STOREでグループ化するように指定します。STOREの一意値それぞれが、結果出力の1行になります。同じグループ化基準値を含む入力行は、ピボット済出力で同じ行にグループ化されます。グループ化基準属性を指定しないと、すべての入力行が結果出力の1行に変換されます。
PRODUCTをピボット・キーとして指定し、3つの値すべてを結果出力の新規属性にピボットするように選択します。
SALESに対する集計SUM関数式は次のとおりです:
SUM(PIVOT_1_1.PRODUCT_SALES.SALES)
ターゲット属性のパターンは次のとおりです:
%PIVOT_KEY_VALUE%
データ・エンティティPRODUCT_SALES
STORE | PRODUCT | SALES |
---|---|---|
AB Store | Television | 2 |
AB Store | Television | 4 |
Country-Store | Television | 6 |
Country-Store | Refrigerator | 8 |
E-Store | Coffee maker | 10 |
ピボット出力: グループ化基準STORE、ピボット・キーPRODUCT
STORE | TELEVISION | REFRIGERATOR | COFFEE MAKER |
---|---|---|---|
AB-Store | 6 | - | - |
Country-Store | 6 | 8 | - |
E-Store | - | - | 10 |
ピボット出力: グループ化基準なし、ピボット・キーPRODUCT
TELEVISION | REFRIGERATOR | COFFEE MAKER |
---|---|---|
12 | 8 | 10 |
ピボット演算子は、ピボット・キーとして指定した属性の1つ以上の値に対して1つ以上の集計関数式を使用して、変換を実行します。
ピボット済行を1つの行にグループ化するか、属性を選択して同じグループ化基準値に基づく複数の出力行を作成するかを選択できます。
ピボット演算子には1つ以上の集計関数式が必要です。
データ・フロー・キャンバス上でピボット演算子が選択された状態で、プロパティ・パネルの「式」で、「式の追加」をクリックします。
「式の追加」パネルで、「識別子」フィールドに式の名前を入力するか、名前をそのままにします。
(オプション)パターンを使用して複数のソース属性に式を適用するには、「バルク選択の許可」を選択します。
たとえば、データセット内に2つのDISCOUNT_属性(DISCOUNT_VALUEおよびDISCOUNT_RATE)があり、両方に
MAX
関数を適用するとします。「ソース属性」で、「パターン」を選択し、「パターンの追加」をクリックします。
「ソース・パターンの追加」パネルで、名前がDISCOUNT_で始まるソース属性のグループを選択するためのパターンを追加します。たとえば、
DISCOUNT*
と入力し、「追加」をクリックします。次に、データ型を選択します。「ターゲット属性」で、結果出力の属性名のパターンを使用します。
デフォルトでは、パターン
%MACRO_INPUT%_%PIVOT_KEY_VALUE%
がすでに挿入されています。%MACRO_INPUT%
は、追加したパターンで選択されるソース属性の名前に対応します。%PIVOT_KEY_VALUE%
は、ピボット・キーの選択された値に対応します。たとえば、
%PIVOT_KEY_VALUE%
がTELEVISIONを示し、%MACRO_INPUT%
がDISCOUNT_VALUEおよびDISCOUNT_RATEを示す場合、出力のピボット済属性は<pivot_name>.<expression_name>.DISCOUNT_VALUE_TELEVISION
と<pivot_name>.<expression_name>.DISCOUNT_RATE_TELEVISION
になります。- 「ソース属性データ型の使用」の選択は保持できます。それ以外の場合は、「式データ型」で「データ型」を選択し、選択した型に対応するフィールドに入力します。
「バルク選択の許可」を選択しなかった場合は、「ターゲット属性」で、結果出力の属性名のためにパターンを使用します。
デフォルトでは、パターン
%PIVOT_KEY_VALUE%
がすでに挿入されています。%PIVOT_KEY_VALUE%
は、ピボット・キーの選択された値に対応します。たとえば、
%PIVOT_KEY_VALUE%
がTELEVISIONとTELEPHONEを示す場合、出力のピボット済属性は<pivot_name>.<expression_name>.TELEVISION
および<pivot_name>.<expression_name>.TELEPHONE
になります。「式データ型」で「データ型」を選択し、選択した型に対応するフィールドに入力します。
「式ビルダー」セクションで、受信属性、パラメータおよび集計機能をダブルクリックまたはドラッグしてエディタに追加し、式を作成します。式を手動で記述して検証することもできます。
次の表に、ピボット式の作成に使用できる集計関数のリストを示します。
関数 説明 例 COUNT(value[, value]*)
指定された1つ以上の式がすべてnullでない行の数を返します。 COUNT(expr1)
COUNT(*)
取得された行の合計数(nullのある行を含む)を返します。 COUNT(*)
MAX(value)
引数の最大値を返します。 MAX(expr)
MIN(value)
引数の最小値を返します。 MIN(expr)
SUM(numeric)
グループの値から計算された合計を返します。 SUM(expr1)
ピボット式でhigher-order (transform)関数を使用することもできます。
ピボット式を作成するには、属性(1つまたは複数)と集計関数を指定します。
「バルク選択の許可」を選択した場合は、式で
%MACRO_INPUT%
を使用して、関数を適用する属性を指定します。たとえば、ソース属性
DISCOUNT_RATE
およびDISCOUNT_VALUE
と一致するパターンDISCOUNT*
を使用した場合は、SUM(numeric)
などの集計ファンクションを指定して、パターンと一致するすべての属性にこのファンクションを適用できます。%MACRO_INPUT%
によってこのファンクションのnumeric
プレースホルダを置き換えます:SUM(%MACRO_INPUT%)
「バルク選択の許可」を選択しなかった場合は、関数に属性を指定します。
たとえば、データ・エンティティがPRODUCT_SALESで、SALES属性に対して集計SUM関数を使用します。
SUM(numeric)
のような関数を指定して、関数のnumeric
プレースホルダを属性名で置き換えることができます:SUM(PIVOT_1_1.PRODUCT_SALES.SALES)
ピボット式の集計関数名には式パラメータを使用できます。式パラメータには、名前、タイプおよびデフォルト値があります。
たとえば、式パラメータ
P_VARCHAR
のデフォルト値は型VARCHAR
およびMIN
です。次のように集計関数を指定できます:$P_VARCHAR(%MACRO_INPUT%)
$P_VARCHAR(PIVOT_1_1.PRODUCT_SALES.SALES)
「式の追加」パネルで「追加」をクリックします。
ルックアップ演算子
ルックアップ演算子は、ルックアップ条件および2つのソース(プライマリ入力ソースとルックアップ入力ソース)からの入力を使用して、問合せと変換を実行します。
問合せ操作では、条件とプライマリ入力の値を使用して、ルックアップ・ソース内で行を探します。変換は、ルックアップ・ソースの属性をプライマリ・ソースに追加します。
ルックアップ問合せによって複数の行が返される場合や行が返されない場合に実行するアクションを指定できます。たとえば、不一致の行をスキップし、複数の一致行があるときは一致する行を1つ返すようにアクションを指定できます。
結果出力では、ルックアップ条件、プライマリ入力の値および実行する優先アクションに基づいて、両方の入力ソースが組み合されます。プライマリ入力によって出力内の属性と行の順序が決まります。プライマリ入力の属性がルックアップ入力の属性より前に配置されます。
データ・フロー内にある2つのソース・データ・エンティティを考えてみます。データ・エンティティ1 (PAYMENTS)はプライマリ入力として設定されています。データ・エンティティ2 (CUSTOMERS)はルックアップ入力として設定されています。ルックアップ条件は次のように設定されています:
LOOKUP_1_1.PAYMENTS.CUSTOMER_ID = LOOKUP_1_2.CUSTOMERS.CUSTOMER_ID
結果のルックアップ出力は、2つの入力からのデータをどのように結合および変換するかを示します。ルックアップ・ソースからの属性は、次の動作でプライマリ・ソース属性に追加されます:
- ルックアップ・ソース内で値と一致するレコードが見つからない場合、ルックアップ属性にnullが挿入されたレコードが返されます。たとえば、CUSTOMER_ID値103、104および105に一致するレコードが見つからなかったとします。この場合、結果の出力では、追加された属性CUSTOMER_IDおよびNAMEにnullが移入されます。
- ルックアップ・ソース内で値と一致するレコードが複数見つかった場合は、一致するレコードが1つ返されます。
データ・エンティティ1、プライマリ入力ソース
PAYMENT_ID | CUSTOMER_ID | AMOUNT |
---|---|---|
1 | 101 | 2500 |
2 | 102 | 1110 |
3 | 103 | 500 |
4 | 104 | 400 |
5 | 105 | 150 |
6 | 102 | 450 |
データ・エンティティ2、ルックアップ入力ソース
CUSTOMER_ID | NAME |
---|---|
101 | Peter |
102 | Paul |
106 | Mary |
102 | Pauline |
ルックアップ出力
PAYMENT_ID | CUSTOMER_ID | AMOUNT | CUSTOMER_ID | NAME |
---|---|---|---|---|
1 | 101 | 2500 | 101 | Peter |
2 | 102 | 1110 | 102 | Paul |
3 | 103 | 500 | null | null |
4 | 104 | 400 | null | null |
5 | 105 | 150 | null | null |
6 | 102 | 450 | 102 | Paul |
ルックアップ演算子は、データ・フローで2つの入力ソースを使用します。
次の手順では、2つのソース演算子が追加および構成されていることを想定しています。
条件ビルダーを使用して要素を視覚的に選択し、ルックアップ条件を作成します。エディタで条件を手動入力することもできます。
ルックアップ条件を使用すると、プライマリ入力ソースの値を使用してルックアップ入力ソースのレコードをルックアップし、一致する行を返すことができます。一致のない行は、null値とともに返されます。
ルックアップ条件で使用できる要素には、受信属性、パラメータおよび関数があります。要素をダブルクリックするかリストからドラッグしてエディタに追加し、条件を作成できます。条件は作成する前に検証できます。
「受信」には、2つの個別のLOOKUPフォルダ内にアップストリーム入力ポートからの属性が表示されます。各ポートからの属性を表示するには、該当するLOOKUPフォルダを展開または縮小します。たとえば、LOOKUP_1_1がプライマリ入力、LOOKUP_1_2がルックアップ入力の場合、プライマリ入力属性ADDRESS_IDの値に基づくルックアップ条件は:
LOOKUP_1_1.BANK_ADDRESS.ADDRESS_ID = '2001'
LOOKUP_1_1.BANK_ADDRESS.ADDRESS_ID = LOOKUP_1_2.BANK_CUSTOMER.ADDRESS_ID
パラメータは、条件ビルダー(フィルタ演算子、結合演算子、ルックアップ演算子および分割演算子)または式ビルダー(式演算子および集計演算子)を使用してデータ・フローに追加された式パラメータです。式パラメータには、名前、タイプおよびデフォルト値があります。式パラメータの追加を参照してください。
特定の銀行の銀行顧客をルックアップするとします。P_LOOK_UP
という名前のVARCHAR
パラメータを作成し、デフォルト値を特定の銀行値である2001
に設定できます。その後、次のようにルックアップ条件を作成できます:
LOOKUP_1_1.BANK_ADDRESS.ADDRESS_ID = LOOKUP_1_2.BANK_CUSTOMER.ADDRESS_ID AND LOOKUP_1_1.BANK_ADDRESS.ADDRESS_ID = $P_LOOK_UP
ファンクションはデータ統合で使用可能な関数で、条件の中で使用できます。ファンクションは、関数に渡される引数に対して実行される操作です。ファンクションは、引数からデータ値を計算、操作または抽出します。
条件の作成時に追加できる関数のリストを次に示します:
関数 | 説明 | 例 |
---|---|---|
MD5(all data types) | データ型のMD5 チェックサムを計算し、文字列値を返します。 | MD5(column_name) |
SHA1(all data types) | データ型のSHA-1 ハッシュ値を計算し、文字列値を返します。 | SHA1(column_name) |
SHA2(all data types, bitLength) | データ型のSHA-2 ハッシュ値を計算し、文字列値を返します。bitLength は整数です。 | SHA2 (column_name, bitLength can be set to 0 (equivalent to 256), 256, 384, or 512) 。 |
ORA_HASH(expr, [max_bucket], [seed_value]) |
Oracleは、ハッシュ関数を |
|
関数 | 説明 | 例 |
---|---|---|
ABS(numeric) | numeric 値の絶対乗を返します。 | ABS(-1) |
CEIL(numeric) | numeric 値を超えない最小の整数を返します | CEIL(-1,2) |
FLOOR(numeric) | numeric 値を超えない最大の整数を返します。 | FLOOR(-1,2) |
MOD(numeric1, numeric2) | numeric1 をnumeric2 で除算した後の剰余を返します。 | MOD(8,2) |
POWER(numeric1, numeric2) | numeric1 をnumeric2 で累乗します。 | POWER(2,3) |
ROUND(numeric1, numeric2) | numeric1 を小数点以下numeric2 桁に丸めて返します。 | ROUND(2.5,0) |
TRUNC(numeric1, numeric2) | numeric1 を小数点以下numeric2 桁に切り捨てて返します。 | TRUNC(2.5,0) |
TO_NUMBER(expr[, format, locale]) | 指定されたformat およびlocale (オプション)に基づいて、expr を数値に変換します。デフォルトのロケールはen-US です。サポートされる言語タグ。サポートされるフォーマット・パターン:
|
|
関数 | 説明 | 例 |
---|---|---|
CURRENT_DATE |
現在の日付を返します。 | CURRENT_DATE は、2023-05-26 などの今日の日付を返します |
CURRENT_TIMESTAMP |
セッション・タイムゾーンに対する現在の日付と時刻を返します。 | CURRENT_TIMESTAMP は、今日の日付と現在の時刻(2023-05-26 12:34:56 など)を返します
|
DATE_ADD(date, number_of_days) |
指定したdate からnumber 日後の日付を返します。 |
DATE_ADD('2017-07-30', 1) は2017-07-31 を返します |
DATE_FORMAT(expr, format[, locale]) |
指定された サポートされる日付フォーマット・パターン:
|
|
DAYOFMONTH(date) |
特定の日(月間)を返します。 | DAYOFMONTH('2020-12-25') は25 を返します。 |
DAYOFWEEK(date) |
特定の日(週間)を返します。 | DAYOFWEEK('2020-12-25') は、金曜日の6 を返します。米国では、日曜日は1、月曜日は2などとみなされます。 |
DAYOFYEAR(date) |
特定の日(年間)を返します。 | DAYOFYEAR('2020-12-25') は360 を返します |
WEEKOFYEAR(date) |
日付が年内の何番目の週かを返します。 |
|
HOUR(datetime) |
日時の時間の値を返します。 | HOUR('2020-12-25 15:10:30') は15 を返します |
LAST_DAY(date) |
月末の日付を返します。 | LAST_DAY('2020-12-25') は31 を返します |
MINUTE(datetime) |
日時の分の値を返します。 | HOUR('2020-12-25 15:10:30') は10 を返します |
MONTH(date) |
日付の月の値を返します。 | MONTH('2020-06-25') は6 を返します。 |
QUARTER(date) |
日付が属する四半期を返します。 | QUARTER('2020-12-25') は4 を返します。 |
SECOND(datetime) |
日時の秒の値を返します。 | SECOND('2020-12-25 15:10:30') は30 を返します |
TO_DATE(string, format_string[, localeStr]) |
format_string 式を含む文字列式を日付に解析します。ロケールはオプションです。デフォルトはen-US です。サポートされる言語タグ。パイプライン式では、
|
|
TO_TIMESTAMP(expr, format_string[, localeStr]) |
指定されたformat_string およびlocaleStr (オプション)に基づいて、VARCHARのexpr をTIMESTAMPの値に変換します。パイプライン式では、
|
TO_TIMESTAMP('2020-10-11 11:10:10', 'yyyy-MM-dd HH:mm:ss') は、11am 10:10 Oct 11th, 2020 を表すTIMESTAMPオブジェクトを返します |
WEEK(date) |
日付の週の値を返します。 |
WEEK('2020-06-25') は4 を返します |
YEAR(date) |
日付の年の値を返します。 | YEAR('2020-06-25') returns 2020 |
ADD_MONTHS(date_expr, number_months) |
指定した日付、タイムスタンプまたは文字列(yyyy-MM-dd またはyyyy-MM-dd HH:mm:ss.SSS のようなフォーマット)に指定した数の月を追加した日付を返します。 |
|
MONTHS_BETWEEN(start_date_expr, end_date_expr) |
整数が返されるのは、両方の日付の日の部分が同じ場合、または両方が月の最終日の場合です。それ以外の場合は、1か月を31日として差が計算されます。 |
|
FROM_UTC_TIMESTAMP(time_stamp, time_zone) |
日付、タイムスタンプまたは文字列をUTC時間として解釈し、その時間を指定したタイムゾーンのタイムスタンプに変換します。 文字列の場合は、 タイム・ゾーンのフォーマットは、リージョンベースのゾーンID(例: 'Asia/Seoul'のような'area/city')またはタイム・ゾーン・オフセット(例: UTC+02)のいずれかです。 |
FROM_UTC_TIMESTAMP('2017-07-14 02:40:00.0', 'GMT+1') は2017-07-14 03:40:00.0 を返します |
TO_UTC_TIMESTAMP(time_stamp, time_zone) |
指定したタイムゾーンの日付、タイムスタンプまたは文字列をUTCタイムスタンプに変換します。 文字列の場合は、 タイム・ゾーンのフォーマットは、リージョンベースのゾーンID(例: 'Asia/Seoul'のような'area/city')またはタイム・ゾーン・オフセット(例: UTC+02)のいずれかです。 |
TO_UTC_TIMESTAMP('2017-07-14 02:40:00.0', 'GMT+1') は2017-07-14 01:40:00.0 を返します
|
FROM_UNIXTIME(unix_time[, fmt]) |
指定したUnix時間すなわちエポックを、現在のシステム・タイム・ゾーンおよび指定したフォーマットで、その時点のタイムスタンプを表す文字列に変換します。 ノート: Unix timeは、1970年1月1日00:00:00 UTCから経過した秒数です。
|
例のデフォルトのタイムゾーンはPSTです |
UNIX_TIMESTAMP([time_expr[, fmt]]) |
現在時刻または指定した時刻をUnixタイムスタンプ(秒単位)に変換します。
|
この例のデフォルトのタイムゾーンはPSTです |
INTERVAL 'year' YEAR[(year_precision)] |
期間を年単位で返します。 year_precisionはyearフィールドの桁数で、範囲は0から9です。year_precisionを省略した場合、デフォルトは2です(100年未満である必要があります)。 |
|
INTERVAL 'year month' YEAR[(year_precision)] TO MONTH |
期間を年と月の単位で返します。yearおよびmonthフィールドを使用して期間を格納するために使用します。 year_precisionはyearフィールドの桁数で、範囲は0から9です。year_precisionを省略した場合、デフォルトは2です(100年未満である必要があります)。 |
INTERVAL '100-5' YEAR(3) TO MONTH は100年と5か月の期間を返します。先頭の年の精度として3を指定する必要があります。 |
INTERVAL 'month' MONTH[(month_precision)] |
期間を月単位で返します。 month_precisionはmonthフィールドの桁数で、範囲は0から9です。month_precisionを省略した場合、デフォルトは2です(100年未満である必要があります)。 |
INTERVAL '200' MONTH(3) は200か月の期間を返します。月の精度として3を指定する必要があります。 |
INTERVAL 'day time' DAY[(day_precision)] TO SECOND[(fractional_seconds_precision)] |
日、時、分、秒で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 fractional_seconds_precisionは、timeフィールドの秒の値の分数部分の桁数であり、範囲は0から9です。 |
INTERVAL '11 10:09:08.555' DAY TO SECOND(3) は、11日10時間9分8秒555ミリ秒の期間を返します |
INTERVAL 'day time' DAY[(day_precision)] TO MINUTE[(minute_precision)] |
日、時、分で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '11 10:09' DAY TO MINUTE は、11日10時間9分の期間を返します |
INTERVAL 'day time' DAY[(day_precision)] TO HOUR[(hour_precision)] |
日数と時間数で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '100 10' DAY(3) TO HOUR は、100日10時間の期間を返します |
INTERVAL 'day' DAY[(day_precision)] |
日数で期間を返します。 day_precisionはdayフィールドの桁数で、範囲は0から9です。デフォルトは2です。 |
INTERVAL '999' DAY(3) は999日の期間を返します |
INTERVAL 'time' HOUR[(hour_precision)] TO SECOND[(fractional_seconds_precision)] |
時、分、秒で期間を返します。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 fractional_seconds_precisionは、timeフィールドの秒の値の分数部分の桁数であり、範囲は0から9です。 |
INTERVAL '09:08:07.6666666' HOUR TO SECOND(7) は、9時間8分7.6666666秒の期間を返します |
INTERVAL 'time' HOUR[(hour_precision)] TO MINUTE[(minute_precision)] |
時間と分数で期間を返します。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '09:30' HOUR TO MINUTE は、9時間30分の期間を返します |
INTERVAL 'hour' HOUR[(hour_precision)] |
時間数で期間を返します。 hour_precisionはhourフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '40' HOUR は40時間の期間を返します |
INTERVAL 'minute' MINUTE[(minute_precision)] |
分数で期間を返します。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 |
INTERVAL '15' MINUTE は15分間の期間を返します |
INTERVAL 'time' MINUTE[(minute_precision)] TO SECOND[(fractional_seconds_precision)] |
分数と秒数で期間を返します。 minute_precisionはminuteフィールドの桁数で、範囲は0から2です。デフォルトは2です。 fractional_seconds_precisionは、timeフィールドの秒の値の分数部分の桁数であり、範囲は0から9です。 |
INTERVAL '15:30' MINUTE TO SECOND は15分30秒の期間を返します |
INTERVAL 'second' SECOND[(fractional_seconds_precision)] |
秒数で期間を返します。 fractional_seconds_precisionは、secondフィールドの分数部分の桁数であり、範囲は0から9です。デフォルトは3です。 |
INTERVAL '15.678' SECOND は15.678秒の期間を返します |
関数 | 説明 | 例 |
---|---|---|
FIRST_VALUE(value) OVER ([ partition_clause ] order_by_clause [ windowFrame_clause ] ) |
ウィンドウ・フレームの最初の行である行で評価された値を返します。 | FIRST_VALUE(BANK_ID) OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) は、現在行とその行の後の1行の間にある行を計算し、BANK_ID でパーティション化し、BANK_NAME で昇順にしたときの、ウィンドウ内のBANK_ID の最初の値を返します。 |
LAG(value[, offset[, default]]) OVER ([ partition_clause ] order_by_clause) |
パーティション内の現在行より前の指定されたオフセットで、行で評価された値を返します。そのような行がない場合は、デフォルト値が返されます。現在の行に基づいて、オフセットとデフォルトの両方が評価されます。省略した場合、オフセットは1に設定され、デフォルトはNULLに設定されます。 | LAG(BANK_ID, 2, 'hello') OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME DESC) は、BANK_ID でパーティション化し、BANK_NAME の降順にしたときの、現在行より2行前のBANK_ID の値を返します。そのような値がない場合は、hello が返されます。 |
LAST_VALUE(value) OVER ([ partition_clause ] order_by_clause [ windowFrame_clause ]) |
ウィンドウ・フレームの最後の行である行で評価された値を返します。 | LAST_VALUE(BANK_ID) OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) は、現在行とその行の後の1行の間にある行を計算し、BANK_ID でパーティション化し、BANK_NAME で昇順にしたときの、ウィンドウ内のBANK_ID の最後の値を返します。 |
LEAD(value[, offset[, default]]) OVER ([ partition_clause ] order_by_clause) |
パーティション内の現在行より後の特定のオフセットにある行で評価された値を返します。そのような行がない場合は、デフォルト値が返されます。現在の行に基づいて、オフセットとデフォルトの両方が評価されます。省略した場合、オフセットは1に設定され、デフォルトはNULLに設定されます。 | LEAD(BANK_ID, 2, 'hello') OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME) は、BANK_ID でパーティション化し、BANK_NAME の昇順にしたときの、現在行より2行後のBANK_ID の値を返します。そのような値がない場合は、hello が返されます。 |
RANK() OVER([ partition_clause ] order_by_clause) |
ギャップを含む現在行のランクを返します(1からカウント)。 | RANK() OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME) は、BANK_ID のパーティション・グループ内の各行のランクをBANK_NAME の昇順で返します。 |
ROW_NUMBER() OVER([ partition_clause ] order_by_clause) |
パーティション内の現在行の一意の番号を返します(1からカウント)。 | ROW_NUMBER() OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME) は、BANK_ID のパーティション・グループ内の各行の一意の行番号をBANK_NAME の昇順で返します。 |
関数 | 説明 | 例 |
---|---|---|
CAST(value AS type) | 指定されたタイプの指定された値を返します。 | CAST("10" AS INT) は10 を返します |
CONCAT(string, string) | 文字列または列を結合した値を返します | CONCAT('Oracle','SQL') はOracleSQL を返します |
CONCAT_WS(separator, expression1, expression2, expression3,...) |
指定したセパレータを使用して、文字列または列を結合した値を文字列または列間で返します。 区切り記号は必須であり、文字列である必要があります。 セパレータの後に少なくとも1つの式を指定する必要があります。たとえば: |
CONCAT_WS('-', 'Hello', 'Oracle') はHello-Oracle を返します
関数の子が配列である場合、配列はフラット化されます。
|
INITCAP(string) | 各単語の最初の文字を大文字、残りの文字をすべて小文字にした文字列を、単語どうしを空白で区切って返します。 | INITCAP('oRACLE sql') はOracle Sql を返します |
INSTR(string, substring[start_position]) | string で最初に出現するsubstring の(1から始まる)索引を返します | INSTR('OracleSQL', 'SQL') は7 を返します |
LOWER(string) | すべての文字を小文字に変更して文字列を返します。 | LOWER('ORACLE') はoracle を返します |
LENGTH(string) | 文字列の文字長またはバイナリ・データのバイト数を返します。文字列の長さには末尾のスペースも含まれます。 | LENGTH('Oracle') は6 を返します |
LTRIM(string) | 先頭のスペースを左から除去して文字列を返します。 | LTRIM(' Oracle') |
NVL(expr1, epxr2) | nullでない引数を返します。 | NVL(EXPRESSION_3.CUSTOMERS_JSON.CONTINENT_ID, ROWID()) |
REGEXP_SUBSTR(string, regexp[, RegexGroupIdx]) | 入力文字列から正規表現パターンに一致する文字列を検索して抽出します。オプションのキャプチャ・グループ索引が指定されている場合、この関数は特定のグループを抽出します。 |
REGEXP_SUBSTR('22;33;44', '([0-9.]*);([0-9.]*);([0-9.]*)', 1) は22 を返します |
REPLACE(string, search, replacement) | 出現するすべてのsearch をreplacement に置換します。文字列に
| REPLACE('ABCabc', 'abc', 'DEF') はABCDEF を返します |
RTRIM(string) | 先頭のスペースを右から除去して文字列を返します。 | RTRIM('Oracle ') |
SUBSTRING(string, position[, substring_length]) | 位置から始まる部分文字列を返します。 | SUBSTRING('Oracle SQL' FROM 2 FOR 3) はrac を返します |
数値の場合、TO_CHAR(expr) および日付の場合、TO_CHAR(expr, format[, locale]) | 数字と日付を文字列に変換します。数値の場合、書式は必要ありません。日付の場合は、「日時関数」で説明されているDATE_FORMAT と同じ形式を使用します。デフォルトのロケールはen-US です。「サポートされている言語タグ」を参照してください。パイプライン式では、
| 数値の例: 日付の例: |
UPPER(string) | すべての文字を大文字に変更して文字列を返します。 | UPPER('oracle') はORACLE を返します |
LPAD(str, len[, pad]) | 文字列の左側に指定した文字を特定の長さまで埋め込んで返します。pad文字を省略した場合、デフォルトは空白です。 | LPAD('ABC', 5, '*') は'**ABC' を返します |
RPAD(str, len[, pad]) | 文字列の右側に指定した文字を特定の長さまで埋め込んで返します。pad文字を省略した場合、デフォルトは空白です。 | RPAD('XYZ', 6, '+' )は'XYZ+++' を返します |
関数 | 説明 | 例 |
---|---|---|
CASE WHEN condition1 THEN result1 ELSE result2 END | 条件を満たす値を返します。 | CASE WHEN 1 > 0 THEN 'ABC' ELSE 'XYZ' END は、1> 0 の場合はABC を返し、それ以外の場合はXYZ を返します |
AND | 論理AND演算子。両方のオペランドがtrueの場合はtrueを返し、それ以外の場合はfalseを返します。 | (x = 10 AND y = 20)は、xが10でyが20の場合、trueを返します。いずれか一方がtrueでない場合は、falseを返します |
OR | 論理OR演算子。いずれかのオペランドがtrueであるか、両方ともtrueである場合はtrueを返し、それ以外の場合はfalseを返します。 | (x = 10 OR y = 20)は、xが10ではなく、かつyが20でない場合、falseを返します。いずれか一方がtrueの場合は、trueを返します |
NOT | 論理NOT演算子。 | |
LIKE | string1がstring2のパターンに一致するかどうかにかかわらず、文字列パターン・マッチングを実行します。 | |
= | 等価かどうかをテストします。expr1がexpr2と等しい場合はtrueを返し、それ以外の場合はfalseを返します。 | x = 10は、xの値が10の場合はtrueを返し、それ以外の場合はfalseを返します |
!= | 非等価かどうかをテストします。expr1がexpr2と等しくない場合はtrueを返し、それ以外の場合はfalseを返します。 | x != 10は、xの値が10の場合はfalseを返し、それ以外の場合はtrueを返します |
> | 式の大なりをテストします。expr1がexpr2より大きい場合は、trueを返します。 | x > 10は、xの値が10より大きい場合はtrueを返し、それ以外の場合はfalseを返します |
>= | 式の大なりイコールをテストします。expr1がexpr2以上の場合は、trueを返します。 | x > =10は、xの値が10以上の場合はtrueを返し、それ以外の場合はfalseを返します |
< | 式の小なりをテストします。expr1がexpr2より小さい場合は、trueを返します。 | x < 10は、xの値が10より小さい場合はtrueを返し、それ以外の場合はfalseを返します |
<= | 式の小なりイコールをテストします。expr1がexpr2以下の場合は、trueを返します。 | x <= 10は、xの値が10より小さい場合はtrueを返し、それ以外の場合はfalseを返します |
|| | 2つの文字列を連結します。 | 'XYZ' || 'hello' は'XYZhello' を返します |
BETWEEN | 範囲を評価します。 | FILTER_1.BANK.BANK_ID BETWEEN 1003 AND 1007 |
IN | 式が値リストと一致するかどうかをテストします。 | FILTER_2.ORDERS.ORDER_ID IN (1003, 1007) |
関数 | 説明 | 例 |
---|---|---|
NUMERIC_ID() | 各行に対して64ビットの数値の汎用一意識別子を生成します。 | NUMERIC_ID() は、たとえば3458761969522180096 および3458762008176885761 を返します |
ROWID() | 単調に増加する64ビット数を生成します。 | ROWID() は、たとえば、0 、1 、2 などを返します |
UUID() | 各行に対して128ビットの文字列の汎用一意識別子を生成します。 | UUID() は、たとえば20d45c2f-0d56-4356-8910-162f4f40fb6d を返します |
MONOTONICALLY_INCREASING_ID() |
単調に増加する一意の64ビット整数を生成します。連続した数字にはなりません。 | MONOTONICALLY_INCREASING_ID() は、たとえば、8589934592 や25769803776 を返します |
関数 | 説明 | 例 |
---|---|---|
COALESCE(value, value [, value]*) | 存在する場合は最初のnullでない引数を返し、それ以外の場合はnullを返します。 | COALESCE(NULL, 1, NULL) は1 を返します |
NULLIF(value, value) | 2つの値が等しい場合はnullを返し、それ以外の場合は最初の値を返します。 | NULLIF('ABC','XYZ') はABC を返します |
関数 | 説明 | 例 |
---|---|---|
SCHEMA_OF_JSON(string) | JSON文字列を解析し、スキーマのDDLフォーマットを推測します。 |
|
FROM_JSON(column, string) | JSON文字列を含む列を解析し、指定スキーマを含む次のいずれかの型に解析します。
|
|
TO_JSON(column) | StructまたはStruct配列あるいはMapまたはMap配列の型を含む列をJSON文字列に変換します。 | TO_JSON(TO_STRUCT('s1', TO_ARRAY(1,2,3), 's2', TO_MAP('key', 'value'))) はJSON文字列{"s1":[1,2,3],"s2":{"key":"value"}} を返します |
TO_MAP(string,column[,string,column]*) | Map型の新しい列を作成します。入力列は、キーと値のペアとしてグループ化する必要があります。入力キー列をNULLにすることはできません。すべて同じデータ型である必要があります。入力値列はすべて同じデータ型である必要があります。 |
|
TO_STRUCT(string,column[,string,column]*) | Struct型の新しい列を作成します。入力列は、キーと値のペアとしてグループ化する必要があります。 |
|
TO_ARRAY(column[,column]*) | Array型の新しい列を作成します。入力列はすべて同じデータ型である必要があります。 |
|
式および階層データ型の作成をサポートするデータ・フロー演算子では、上位の関数を使用できます。
サポートされている演算子は次のとおりです:
-
集計
-
式
-
フィルタ
-
結合
-
検索
-
分割済
-
ピボット
関数 | 説明 | 例 |
---|---|---|
TRANSFORM(column, lambda_function) | 配列と無名関数を取得し、各要素に関数を適用して結果を出力配列に割り当てることによって、新しい配列を設定します。 | 整数[1, 2, 3] の入力配列の場合、TRANSFORM(array, x -> x + 1) は[2, 3, 4] の新しい配列を返します。 |
TRANSFORM_KEYS(column, lambda_function) | 2つの引数(キーと値)を持つマップと関数を取得し、キーがラムダ関数の結果のタイプを持ち、値が列マップ値のタイプを持つマップを返します。 | 整数キーおよび文字列値が{1 -> 'value1', 2 -> 'value2', 3 -> 'value3'} の入力マップの場合、TRANSFORM_KEYS(map, (k, v) -> k * 2 + 1) は{3 -> 'value1', 5 -> 'value2', 7 -> 'value3'} の新しいマップを返します。 |
TRANSFORM_VALUES(column, lambda_function) | 2つの引数(キーと値)を持つマップと関数を取得し、値がラムダ関数の結果のタイプを持ち、キーが列マップ・キーのタイプを持つマップを返します。 | 文字列キーおよび文字列値が{'a' -> 'value1', 'b' -> 'value2', 'c' -> 'value3'} の入力マップの場合、TRANSFORM_VALUES(map, (k, v) -> k || __ '' || v) は{'a' -> 'a_value1', 'b' -> 'b_value2', 'c' -> 'c_value3'} の新しいマップを返します。 |
ARRAY_SORT(array(...), lambda_function) |
式演算子のみが 配列を取得し、2つの引数を取る指定された関数に従ってソートします。 この関数は、最初のエレメントが2番目のエレメントより小さいか、等しいか、または大きいかに応じて-1、0、または1を返す必要があります。 関数を省略すると、配列は昇順にソートされます。 |
返される配列は次のとおりです。 [1,5,6] |
ファンクション演算子
ファンクション演算子を使用して、データ統合内からOracle Cloud Infrastructure Functionsを呼び出します。
マップ、配列およびコンポジット(Struct)などの複雑なデータ構造では、JSONシリアライズ形式のみが現在サポートされています。
データ統合のデータ・フロー内からOCI関数を使用する前に、依存関係を理解し、前提条件のタスクを完了していることを確認してください。
呼び出そうとするファンクションをOCIファンクションにデプロイする必要があります。このファンクションは任意の言語で記述できます。
次のタスク・チェックリストを使用して、ファンクション演算子をOCIファンクションとともに使用するために必要な設定および情報があることを確認します。
タスク | 要件 |
---|---|
OCI Functionsの使用とアクセスのための設定 | このトピックでは、テナンシおよび開発環境が、OCIファンクションにファンクションをデプロイできるようにすでに設定されていることを前提としています。 Preparing for Functionsを参照してください。 OCIファンクションにデプロイされているファンクションのみが、データ統合でファンクション演算子とともに使用できます。 |
ファンクションの呼出しおよび管理を制御するポリシーを作成します。 | このトピックでは、ユーザーまたはテナンシ管理者が、必要なOracle Cloud Infrastructureポリシーをすでに作成していることを前提としています。「ネットワークおよびファンクション関連リソースへのアクセスを制御するポリシーの作成」を参照してください。 本番環境では、場合によっては、特定のアプリケーションでファンクションを呼び出すように、または特定のファンクションのみを呼び出すようにユーザーを制限する必要があります。 たとえば、特定のワークスペースでファンクションを呼び出すようにユーザーを制限するには、次の形式でポリシー・ステートメントを入力します:
ワークスペースで特定の関数を呼び出すようにユーザーを制限するには:
ファンクションを呼び出して管理するためのアクセス権の制御を参照してください。 |
OCI Functionsでアプリケーションを作成します。 | アプリケーションは、ファンクションの論理グループです。 アプリケーションには、ファンクションを実行するサブネットを1つから3つまで指定します。あるアプリケーションで実行されているファンクションは、別のアプリケーションで実行されているファンクションから分離されます。 「アプリケーションの作成(関数内)」を参照してください。 |
OCI Functionsへの関数のデプロイ |
OCI Functionsの関数がデータ・フローのデータ統合関数演算子と連携するためには、この関数はスペース文字を含む識別子の読取りまたは書込みを行わない必要があります。 Fn Project CLIを使用してOCI関数にデプロイする場合、その関数はDockerイメージとして作成され、指定されたDockerレジストリにプッシュされます。 ファンクションの作成およびデプロイを参照してください。 |
デプロイされたファンクションをファンクション演算子で使用するために必要な情報を収集します。 | データ統合データ・フローでファンクション演算子を追加して構成する際に、次を把握している必要があります:
|
ファンクション演算子を使用すると、OCIファンクションにデプロイされたファンクションを使用して、入力ソースのデータを処理できます。プリミティブ・データ型および複合データ型がサポートされています。
ファンクションの入力シェイプと、読取りと書込みを行うファンクション演算子の入力属性および出力属性を指定します。次に、手動でソース属性を入力属性にマップします。
現在、JSONシリアライズ形式のみが、マップ、配列およびコンポジット(Struct)などの複雑なデータ構造でサポートされています。
次の手順では、ファンクションで使用するデータ・エンティティに対して、ソース演算子を追加して構成したことを前提としています。
ファンクション演算子で使用するファンクションは、Oracle Cloud Infrastructure Functionsのアプリケーションにデプロイする必要があります。
- キャンバスで、ファンクション演算子を選択します。
- 「プロパティ」パネルの「詳細」タブで、「OCIファンクション」について「選択」をクリックします。
- パネルで、使用するファンクションがデプロイされているOCIファンクション・アプリケーションがあるコンパートメントを選択します。
- デプロイされたファンクションが含まれる、OCIファンクションのアプリケーションを選択します。
- 「OCIファンクション」セクションで、ファンクションを選択します。
- 「OK」をクリックします。
デプロイされたファンクションを選択したら、ファンクションが処理する入力データを定義するプロパティ、ファンクション構成のフィールド、およびファンクションが返す出力データを指定します。
入力属性、出力属性または関数フィールドの識別子名に空白文字を使用しないでください。また、「要素」、「キー」、「値」などの名前は許可されません。
- 「ファンクション・プロパティ」で、「プロパティの追加」をクリックします。
- パネルで、指定するプロパティの「タイプ」を選択します。
- 入力属性: 演算子の入力シェイプ。ファンクションで処理する受信属性を表す1つ以上の属性を指定します。入力属性は、データ・エンティティ・ソースからの受信属性にマップされます。
- ファンクション構成: ファンクションの入力シェイプを定義する1つ以上のファンクション・フィールドを指定します。フィールドには名前と値があります。フィールドの値によって入力属性が指定されます。
- 出力属性: 演算子の出力シェイプ。データが処理された後のファンクションからの出力を表す0個以上の属性を指定します。出力に必要なファンクション・フィールドごとに出力属性を追加します。後続のダウンストリーム演算子が、出力属性を使用できます。
- 「識別子」フィールドにプロパティの名前を入力します。入力属性、出力属性または関数フィールドの名前に空白文字を使用しないでください。
- プロパティのデータ型を選択します。
プリミティブ・データ型の場合は、指定するプロパティの「タイプ」および「データ型」に応じて、次のフィールドおよびその他の該当するフィールドを指定します。
- 長さ: 入力属性または出力属性について長さを入力します。たとえば、numericまたはvarcharデータ型の長さを指定します。
- 値: ファンクション・フィールドの場合は、入力属性と一致する値を入力します。
Map複合データ型の場合は、マップ要素のキーと値のペアを指定します。「データ型の追加」をクリックして、キーのデータ型と値のデータ型を選択します。
- キー: プリミティブ・データ型のみから選択します。
- 値: プリミティブ・データ型と複合データ型から選択します。
Array複合データ型の場合は、「データ型の追加」をクリックしてElementデータ型を指定します。プリミティブ・データ型と複合データ型から選択できます。
Composite (Struct)データ型の場合は、スキーマの子(1つまたは複数)を指定します。スキーマの子を追加するには、親スキーマ名の横にある+記号をクリックします。
スキーマの子の「フィールドの追加」パネルで、「識別子」に入力し、データ型を選択します。プリミティブ・データ型と複合データ型から選択できます。「追加」をクリックして、スキーマの子を追加します。
複数のスキーマの子を親スキーマに追加する場合は、+記号を再度クリックします。
スキーマの子を編集または削除するには、行末のアクション・メニュー(
)を使用します。
親スキーマ要素は削除できません。
- 「プロパティの追加」パネルで「追加」をクリックします。
- ステップを繰り返して、ファンクション入力、ファンクション・フィールドおよびファンクション出力を定義するために必要なプロパティを追加します。
フラット化演算子
フラット化演算子を使用して、階層データを他のリレーショナル・データで使用するために簡略化された形式に変換します。フラット化プロセスは、非正規化またはネスト解除とも呼ばれます。
非正規化できる階層ファイル形式は次のとおりです。
- JSONおよび複数行JSON
- Avro
- Parquet
現在、ネスト解除できるサポートされている階層データ型はArrayです。階層データセット内の1つの配列ノードのみを選択してフラット化できます。データ構造は、ルートから選択した配列ノードにフラット化され、簡略化された形式で表示されます。データセットに他の配列ノードがある場合は、それらを文字列に変換するオプションがあります。
「予測プリファレンス」を設定することで、フラット化された出力に含める属性またはフィールドを構成できます。
予測プリファレンスは、受信データのフラット化後に出力に含める予測属性のフォーマット、数値およびタイプの設定です。
データ統合では、デフォルトで選択される4つのプロジェクト・プリファレンス設定が提供され、次の出力が予測されます。
- 索引配列
- フラット化用に選択された属性までのすべての属性
- 後続の配列にない属性のNull値
- 属性名の親の名前の系統
構成できる投影プリファレンスを理解するには、次のトピックをJSONデータ構造の例とともに使用します。
JSONデータ構造の例
id
vehicle[]
make
model
insurance{}
company
policy_number
maintenance[]
date
description[]
dealerdetails[]
servicestation1[]
dealeraddress[]
city
state
contact
dealername
servicestation2[]
dealeraddress[]
city
state
contact
dealername
投影プリファレンス設定の「配列索引の作成およびプロジェクト」では、フラット化された出力に配列の索引属性を含めるかどうかを制御できます。
index属性は、配列の索引を表します。配列が["a","b","c"]
で、aの索引が0で、bの索引が1で、cの索引が2であるとします。
デフォルトでは、データのフラット化先となる配列ノードの接尾辞_INDEX
を持つ属性がデータ統合によって作成されます。配列索引属性のデータ型はIntegerです。
選択した配列ノードの親配列ノードに対しても索引属性が作成されます。フラット化操作は、ルートから選択したノードまでの階層構造内のすべての親配列ノードに影響します。影響を受ける親配列ノードに兄弟配列がある場合、それらの兄弟配列ノードに対して索引属性は作成されません。
JSONデータ構造の例では、フラット化のためにservicestation2
の下にある配列dealeraddress
を選択すると、データ統合によって5つの配列索引属性が作成されます。1つは選択した配列ノード用、4つはフラット化操作の影響を受ける親ノード用です。
フラット化後、簡易構造の属性は次のようになります。
id
vehicle_INDEX
vehicle_make
vehicle_model
vehicle_insurance
company
policy_number
vehicle_maintenance_INDEX
vehicle_maintenance_date
vehicle_maintenance_description
vehicle_maintenance_dealerdetails_INDEX
vehicle_maintenance_servicestation1
dealeraddress
city
state
contact
dealername
vehicle_maintenance_servicestation2_INDEX
vehicle_maintenance_servicestation2_dealeraddress_INDEX
vehicle_maintenance_servicestation2_dealeraddress_city
vehicle_maintenance_servicestation2_dealeraddress_state
vehicle_maintenance_servicestation2_dealeraddress_contact
vehicle_maintenance_servicestation2_dealeraddress_dealername
配列ノードは影響を受ける親ノードservicestation2
の兄弟であるため、servicestation1
の配列索引属性は作成されません。同様に、description
の索引属性は作成されません。これは、配列ノードが影響を受ける親ノードdealerdetails
の兄弟であるためです。
投影プリファレンス「配列索引の作成とプロジェクト」を選択しない場合、簡易構造の属性は次のようになります。
id
vehicle_make
vehicle_model
vehicle_insurance
company
policy_number
vehicle_maintenance_date
vehicle_maintenance_description
vehicle_maintenance_servicestation1
dealeraddress
city
state
contact
dealername
vehicle_maintenance_servicestation2_dealeraddress_city
vehicle_maintenance_servicestation2_dealeraddress_state
vehicle_maintenance_servicestation2_dealeraddress_contact
vehicle_maintenance_servicestation2_dealeraddress_dealername
投影プリファレンス設定の「フラット化された配列までのすべての属性を保持」では、フラット化された配列の一部ではない属性をフラット化された出力に含めるかどうかを制御できます。
デフォルトでは、データ統合には、フラット化の影響を受けない兄弟配列を含め、ルートから選択した配列までのすべての属性が表示されます。
JSONデータ構造の例で、予測プリファレンス「フラット化された配列までのすべての属性を保持」を選択しない場合、簡易構造の属性は次のようになります。
id
vehicle_INDEX
vehicle_maintenance_INDEX
vehicle_maintenance_dealerdetails_INDEX
vehicle_maintenance_servicestation2_INDEX
vehicle_maintenance_servicestation2_dealeraddress_INDEX
vehicle_maintenance_servicestation2_dealeraddress_city
vehicle_maintenance_servicestation2_dealeraddress_state
vehicle_maintenance_servicestation2_dealeraddress_contact
投影プリファレンスの設定「欠落している親配列に対してNULL値を持つ単一行を生成」では、フラット化の影響を受ける属性を持たない行をスキップするかどうかを制御できます。
「欠落している親配列に対してNULL値を持つ単一行の生成」設定の効果は、「データ」タブにのみ表示されます。デフォルトでは、データ統合には、後続の配列にない属性のnull値が表示されます。
たとえば、NULL値を含む予測出力は次のようになります。
|Company1|Redmond |[...]|true |[...]|0 |1 |123.34 |0 |Laptop |20 |
|Company1|Redmond |[...]|true |[...]|0 |1 |123.34 |1 |Charger |2 |
|Company1|Redmond |[...]|true |[...]|1 |2 |323.34 |0 |Mouse |2 |
|Company1|Redmond |[...]|true |[...]|1 |2 |323.34 |1 |Keyboard |1 |
|Company2|Seattle |[...]|false |[...]|0 |4 |123.34 |0 |Laptop |20 |
|Company2|Seattle |[...]|false |[...]|0 |4 |123.34 |1 |Charger |3 |
|Company2|Seattle |[...]|false |[...]|1 |5 |343.24 |0 |Chair |4 |
|Company2|Seattle |[...]|false |[...]|1 |5 |343.24 |1 |Lamp |2 |
|Company3|Bellevue|null |true |[...]|0 |4 |123.34 |null |null |null |
|Company4|Kirkland|null |null |null |null |null |null |null |null |null |
このオプションを選択しない場合、行はスキップされ、予測される出力は次のようになります。
|Company1|Redmond |[...]|true |[...]|0 |1 |123.34 |0 |Laptop |20 |
|Company1|Redmond |[...]|true |[...]|0 |1 |123.34 |1 |Charger |2 |
|Company1|Redmond |[...]|true |[...]|1 |2 |323.34 |0 |Mouse |2 |
|Company1|Redmond |[...]|true |[...]|1 |2 |323.34 |1 |Keyboard |1 |
|Company2|Seattle |[...]|false |[...]|0 |4 |123.34 |0 |Laptop |20 |
|Company2|Seattle |[...]|false |[...]|0 |4 |123.34 |1 |Charger |3 |
|Company2|Seattle |[...]|false |[...]|1 |5 |343.24 |0 |Chair |4 |
|Company2|Seattle |[...]|false |[...]|1 |5 |343.24 |1 |Lamp |2 |
投影プリファレンス設定の「属性名に親の名前系統を保持」では、子属性名に親名を含めるかどうかを制御できます。
デフォルトでは、データ統合によって、親ノード名を持つ子ノード名が作成されます。
JSONデータ構造の例では、簡易構造内の親名を持つ属性は次のようになります(フラット化の影響を受けない兄弟配列が出力から除外される場合)。
id
vehicle_INDEX
vehicle_maintenance_INDEX
vehicle_maintenance_dealerdetails_INDEX
vehicle_maintenance_servicestation2_INDEX
vehicle_maintenance_servicestation2_dealeraddress_INDEX
vehicle_maintenance_servicestation2_dealeraddress_city
vehicle_maintenance_servicestation2_dealeraddress_state
vehicle_maintenance_servicestation2_dealeraddress_contact
投影プリファレンス「属性名に親の名前系統を保持」を選択しない場合、属性は次のようになります。
id
vehicle_INDEX
maintenance_INDEX
dealerdetails_INDEX
servicestation2_INDEX
dealeraddress_INDEX
city
state
contact
次の手順では、ソース演算子を追加して、JSONファイルなどの複雑なデータ型を持つオブジェクト・ストレージ・ソースに演算子を構成していることを前提としています。
フラット化のために1つの複合属性を選択した後、フラット化構造に他の配列ノードが存在する場合があります。
フラット化された構造体内の他の配列ノードでは、プリミティブ・データ型の配列または構造体の配列を文字列に変換できます。現在、サポートされている変換文字列の形式はJSONです。
データに応じて、変換文字列のデータ型はVARCHARまたはBLOBになります。VARCHARからCLOBへの変換文字列を、逆にCLOBからVARCHARに変換できます。
次の手順では、フラット化演算子を追加し、複合属性を選択してフラット化データ構造を作成することを前提としています。
次の手順では、文字列に変換する配列ノードを選択していることを前提としています(該当する場合)。
データ・フローでフラット化演算子を構成する場合、「フラット化基準」パスをクリアしてフラット化を元に戻すことができます。
テーブル・ファンクション演算子
テーブル・ファンクション演算子を使用して、表の形式でデータを返すテーブル・ファンクションを追加します。
現在、リストから選択できるのは組込み表関数のみです。テーブル・ファンクション演算子によって提供されるテーブル・ファンクションには、キューブ、クロス集計分析、頻度の高いアイテム、ロールアップおよびSpark SQLが含まれます。各組込みテーブル・ファンクションには、目的のデータ・コレクションを返すように構成する事前定義済フィールドがあります。複雑なロジックは、特定のデータ・セットを返す単一の関数に統合できます。フィールドのパラメータ化により、ロジックを柔軟に調整できます。
テーブル・ファンクション演算子は、データ・フロー内の他の演算子と接続できます。したがって、テーブル・ファンクション演算子は、データ・ソース、ミッドストリーム演算子およびターゲットとして使用できます。
テーブル・ファンクション演算子でサポートされている組込みテーブル・ファンクションのリストを次に示します。
指定されたメモリー・ストレージlevel
に従ってデータセットを保持およびキャッシュします。
キャッシュされた自己回復分散データセット(RDD)に新しいデータセットを返します。
パラメータ | 説明 |
---|---|
level |
使用するメモリー・ストレージ:
|
指定された2列の個別値から、ペア単位の頻度表または偶発表を計算します。
各列の個別値の数は、1e4より小さくする必要があります。
偶発表を含むデータフレームを返します。偶発テーブル:
- 最初の列の名前は
col1_col2
で、col1
は最初の列、col2
は変換前の2番目の列です。各行の最初の列は、col1
の個別値です。 - その他の列名は、
col2
の個別値です。 - カウントは
long
タイプとして返されます。 - 発生なしのペアは、カウントとしてゼロになります。
- ゼロ以外のペアの最大数は1e6です。
- NULL要素は
null
に置き換えられます。
パラメータ | 説明 |
---|---|
col1 |
最初の列の名前。制限事項については、この表の後のノートを参照してください。
|
col2 |
2番目の列の名前。制限事項については、この表の後のノートを参照してください。
|
列名で使用できるのは次の文字のみです:
- 大文字と小文字
a-z
A-Z
- 数値
0-9
- アンダースコア
_
例
col1
がage
で、col2
がsalary
であるとします。
変換前:
+---+------+
|age|salary|
+---+------+
|1 |100 |
|1 |200 |
|2 |100 |
|2 |100 |
|2 |300 |
|3 |200 |
|3 |300 |
+---+------+
変換後:
+----------+---+---+---+
|age_salary|100|200|300|
+----------+---+---+---+
|2 |2 |0 |1 |
|1 |1 |1 |0 |
|3 |0 |1 |1 |
+----------+---+---+---+
指定された列リストおよび指定された集計関数式の計算を使用して、可能なすべての組合せの多次元キューブを生成します。
式では、次の集計関数がサポートされています。
AVG
COUNT
MEAN
MAX
MIN
SUM
キューブ変換後にデータフレームを返します。
パラメータ | 説明 |
---|---|
cubeColumns |
マルチディメンション・キューブを生成するための列のリストをカンマで区切ります。 |
aggExpressions |
列に対して実行する集計関数式。 たとえば: |
例
変換前:
+----------+-----+-------+------+---+
|department|group|region |salary|age|
+----------+-----+-------+------+---+
|Eng |A |local |1000 |10 |
|Art |B |foreign|2000 |11 |
|Sport |C |local |1500 |13 |
|Eng |A |foreign|800 |19 |
|Art |A |foreign|3000 |30 |
|Eng |E |foreign|1000 |21 |
|Art |D |local |1000 |32 |
|Sport |C |local |1000 |28 |
+----------+-----+-------+------+---+
cubeColumns
がdepartment, region
で、aggExpressions
がsalary -> avg, age -> max
(平均給与および最大年齢を計算)の場合、変換後のキューブは次のようになります。
+----------+-------+-----------------+--------+
|department|region |avg(salary) |max(age)|
+----------+-------+-----------------+--------+
|Eng |foreign|900.0 |21 |
|Sport |null |1250.0 |28 |
|Art |null |2000.0 |32 |
|Eng |null |933.3333333333334|21 |
|null |local |1125.0 |32 |
|Art |foreign|2500.0 |30 |
|null |foreign|1700.0 |30 |
|Eng |local |1000.0 |10 |
|null |null |1412.5 |32 |
|Sport |local |1250.0 |28 |
|Art |local |1000.0 |32 |
+----------+-------+-----------------+--------+
指定されたファイル形式およびコンテンツを使用して合成データを生成します。サポートされているファイル形式は次のとおりです。
- CSV
- JSON
データ・エンティティを返します。合成データ・ソースは、実データが使用または移動されないパイプライン・ユニット・テストで使用できます。
パラメータ | 説明 |
---|---|
format |
ファイルのタイプ。サポートされている値:
|
content |
指定されたフォーマットのファイル・コンテンツ。 |
例
CSVコンテンツの例:
co1,co2,co3
1,B,3
A,2,C
データ生成結果:
+---+---+---+
|co1|co2|co3|
+---+---+---+
|1 |B |3 |
+---+---+---+
|A |2 |C |
+---+---+---+
指定された1つ以上の列リストで重複を検索し、重複行が削除された新しいデータセットを返します。
パラメータ | 説明 |
---|---|
columns |
列名または列名のカンマ区切りリスト。 |
例
変換前:
+---+---+
|a |b |
+---+---+
|1 |2 |
|3 |4 |
|1 |3 |
|2 |4 |
+---+---+
columns
がa
の場合、変換後のデータセットは次のようになります。
+---+---+
|a |b |
+---+---+
|1 |2 |
|3 |4 |
|2 |4 |
+---+---+
指定された最小頻度を使用して、指定された列または列リスト内の頻度の高い項目を検索します。
Karl et al.によって提案された頻繁な要素数アルゴリズムは、頻繁な項目を見つけるために使用されます。偽陽性はありえます。
各列の頻度の高い項目の配列を持つデータフレームを返します。
パラメータ | 説明 |
---|---|
freqItems |
頻繁な項目を検索する列名または列名のカンマ区切りリスト。 |
support |
アイテムが頻繁に考慮される最小頻度。 値は、1e-4 ( たとえば、 |
例
変換前:
+-------+--------------+
|user_id|favorite_fruit|
+-------+--------------+
| 1| apple|
| 2| banana|
| 3| apple|
| 4| orange|
| 5| banana|
| 6| banana|
| 7| apple|
+-------+--------------+
freqItems
がfavorite_fruit
で、support
が0.6
の場合、変換後に返されるデータフレームは次のとおりです。
+------------------------+
|favorite_fruit_freqItems|
+------------------------+
| [banana]|
+------------------------+
指定された列リストにNULL値またはNaN値を含む行を削除します。
行を除外するデータフレームを返します。
パラメータ | 説明 |
---|---|
how |
行を削除するかどうかを決定する方法。サポートされている値:
|
cols |
列名または列名のカンマ区切りリスト。 |
minNonNulls |
行に含めることができる、nullおよびNaN以外の値の最小数。 指定した最小値より小さい行を削除します。
|
例
変換前:
+----+-------+----+
|id |name |age |
+----+-------+----+
|1 |Alice |25 |
|2 |null |28 |
|null|Bob |null|
|4 |Charlie|30 |
+----+-------+----+
how
がany
で、cols
がname
の場合、変換後に返されるデータフレームは次のとおりです。
+----+-------+----+
|id |name |age |
+----+-------+----+
|1 |Alice |25 |
|null|Bob |null|
|4 |Charlie|30 |
+----+-------+----+
how
パラメータを値all
とともに使用すると、すべての値がNULLの場合にのみ行が削除されます。たとえば、変換前:
+----+--------+----+
| id| name| age|
+----+--------+----+
| 1|John Doe| 30|
| 2| null|null|
| 3|Jane Doe| 25|
| 4|Jake Doe|null|
|null| null|null|
+----+--------+----+
変換後:
+---+--------+----+
| id| name| age|
+---+--------+----+
| 1|John Doe| 30|
| 2| null|null|
| 3|Jane Doe| 25|
| 4|Jake Doe|null|
+---+--------+----+
NULL値を置換し、置換された値を使用してデータフレームを返します。
パラメータ | 説明 |
---|---|
replacement |
NULL値の置換に使用するキー/値マップ。キーは列名です。値は置換値です。 置換値を指します。たとえば、 置換値は列のデータ型にキャストされます。置換値は、次のタイプである必要があります。
|
例
変換前:
+----+-------+----+
|id |name |age |
+----+-------+----+
|1 |Alice |25 |
|2 |null |28 |
|null|Bob |null|
|4 |Charlie|30 |
+----+-------+----+
キー/値マップは次のようになります。
id -> 3
name -> unknown
age -> 10
変換後:
+---+-------+---+
|id |name |age|
+---+-------+---+
|1 |Alice |25 |
|2 |unknown|28 |
|3 |Bob |10 |
|4 |Charlie|30 |
+---+-------+---+
指定されたキーおよび置換値マップのキーを照合して、指定された列内の別の値で値を置換します。
置換された変換を含む新しいデータフレームを返します。
パラメータ | 説明 |
---|---|
cols |
置換値を適用する列名または列名のカンマ区切りリスト。
|
replacement |
使用する置換キー/値マップ。キーは置換する値です。値は置換値です。マップ値にはNULLを指定できます。 置換値を指します。たとえば、 キーと置換値のペアは同じタイプである必要があります。次のタイプのみがサポートされます。
|
例
変換前:
+----+-------+----+
|id |name |age |
+----+-------+----+
|1 |Alice |25 |
|2 |null |28 |
|null|Bob |null|
|4 |Charlie|30 |
+----+-------+----+
cols
がname
で、replacement
がAlice -> Tom
の場合、変換後のデータフレームは次のようになります。
+----+-------+----+
|id |name |age |
+----+-------+----+
|1 |Tom |25 |
|2 |null |28 |
|null|Bob |null|
|4 |Charlie|30 |
+----+-------+----+
指定された列でデータセットを指定された数のパーティションに分割します。
ハッシュ・パーティション化された新しいデータセットを返します。指定されたパーティションの正確な数が返されます。
パラメータ | 説明 |
---|---|
partitionColumns |
データセットがパーティション化される列名の列名またはカンマ区切りリスト。 |
numberOfPartitions |
作成するパーティションの数。 |
指定された列リストおよび指定された集計関数式の計算を使用して、可能な組合せの多次元ロールアップを生成します。
式では、次の集計関数がサポートされています。
AVG
COUNT
MEAN
MAX
MIN
SUM
ロールアップ変換後にデータフレームを返します。
パラメータ | 説明 |
---|---|
rollupColumns |
マルチディメンション・ロールアップを生成するための列のリスト(カンマ区切り)。 |
aggExpressions |
列に対して実行する集計関数式。 たとえば: |
例
変換前:
+----------+-----+-------+------+---+
|department|group|region |salary|age|
+----------+-----+-------+------+---+
|Eng |A |local |1000 |10 |
|Art |B |foreign|2000 |11 |
|Sport |C |local |1500 |13 |
|Eng |A |foreign|800 |19 |
|Art |A |foreign|3000 |30 |
|Eng |E |foreign|1000 |21 |
|Art |D |local |1000 |32 |
|Sport |C |local |1000 |28 |
+----------+-----+-------+------+---+
rollupColumns
がdepartment, region
で、aggExpressions
がsalary -> avg, age -> max
(平均給与および最大年齢を計算)の場合、変換後のロールアップは次のようになります。
+----------+-------+-----------------+--------+
|department|region |avg(salary) |max(age)|
+----------+-------+-----------------+--------+
|Eng |foreign|900.0 |21 |
|Sport |null |1250.0 |28 |
|Art |null |2000.0 |32 |
|Eng |null |933.3333333333334|21 |
|Art |foreign|2500.0 |30 |
|Eng |local |1000.0 |10 |
|null |null |1412.5 |32 |
|Sport |local |1250.0 |28 |
|Art |local |1000.0 |32 |
+----------+-------+-----------------+--------+
最初に指定した表名を使用して一時ビューを作成することにより、受信データに対してSpark SQL問合せを実行します。
パラメータ | 説明 |
---|---|
SQL |
実行するSQL文またはスクリプト。 例: |
tableName |
Sparkが一時表を作成する際に使用する表名または表名のカンマ区切りリスト。 例: |
各層に指定されたサンプリング率に基づいて、置換なしで層別サンプルを生成します。
層別サンプルを表す新しいデータフレームを返します。
パラメータ | 説明 |
---|---|
strata_col |
strataを定義する列。 |
fractions |
たとえば、 小数点を指定しない場合、ゼロとみなされます。 |
sample_size |
|
seed |
|
例
変換前:
+---+-----+
|key|value|
+---+-----+
| 1| 1|
| 1| 2|
| 2| 1|
| 2| 1|
| 2| 3|
| 3| 2|
| 3| 3|
+---+-----+
strata_col
がkey
で、fractions
が次の場合:
1 -> 1.0, 3 -> 0.5
変換後:
+---+-----+
|key|value|
+---+-----+
| 1| 1|
| 1| 2|
| 3| 2|
+---+-----+
数値列および文字列列について指定された統計を計算します。
統計が指定されていない場合、次のすべてが計算されます。
COUNT
MEAN
STDDEV
MIN
- おおよその四分位数(パーセンタイルは25%、50%、75%)
MAX
パラメータ | 説明 |
---|---|
statistics |
統計のカンマ区切りリスト。 サポートされている値:
例: |
例
変換前:
+----------+------+-----------------+--------+
|department|gender|avg(salary) |max(age)|
+----------+------+-----------------+--------+
|Eng |female|900.0 |21 |
|Sport |null |1250.0 |28 |
|Art |null |2000.0 |32 |
|Eng |null |933.3333333333334|21 |
|Art |female|2500.0 |30 |
|Eng |male |1000.0 |10 |
|null |null |1412.5 |32 |
|Sport |male |1250.0 |28 |
|Art |male |1000.0 |32 |
+----------+------+-----------------+--------+
statistics
がcount, mean, stddev, min, 27%, 41%, 95%, max
であるとします。
変換後:
+-------+----------+-----+------+----------------+----------------+
|summary|department|group|gender|salary |age |
+-------+----------+-----+------+----------------+----------------+
|count |8 |8 |8 |8 |8 |
|mean |null |null |null |1412.5 |20.5 |
|stddev |null |null |null |749.166203188585|8.76682056718072|
|min |Art |A |female|800 |10 |
|27% |null |null |null |1000 |13 |
|41% |null |null |null |1000 |19 |
|95% |null |null |null |3000 |32 |
|max |Sport |E |male |3000 |32 |
+-------+----------+-----+------+----------------+----------------+