ETLテスト–面接の質問
ETLは、Extract、Transform、およびLoadの略です。これは、データウェアハウスシステムの重要な概念です。Extraction トランザクションシステムやアプリケーションなどのさまざまなデータソースからデータを抽出することを表します。 Transformation分析レポートに適したデータに変換ルールを適用することを意味します。ザ・loading このプロセスでは、データをターゲットシステム（通常はデータウェアハウス）に移動します。
ETLサイクルに関係する3つの層は次のとおりです。
Staging Layer −ステージング層は、さまざまなソースデータシステムから抽出されたデータを格納するために使用されます。
Data Integration Layer −統合レイヤーは、ステージングレイヤーからデータを変換し、データをデータベースに移動します。データベースでは、データは階層グループに配置されます。 dimensions、そして事実と集約された事実に。DWシステムのファクトテーブルとディメンションテーブルの組み合わせは、schema。
Access Layer −アクセスレイヤーは、分析レポート用のデータを取得するためにエンドユーザーによって使用されます。
ETLツールは、さまざまなデータソースからデータを抽出し、データを変換して、DWシステムにロードするために使用されます。対照的に、BIツールは、エンドユーザー向けのインタラクティブなアドホックレポート、上級管理職向けのダッシュボード、月次、四半期、および年次の取締役会のデータ視覚化を生成するために使用されます。
最も一般的なETLツールには次のものがあります-SAPBO Data Services（BODS）、Informatica、Microsoft – SSIS、Oracle Data Integrator ODI、Talend Open Studio、CloverETLオープンソースなど。
最も一般的なBIツールには、SAP Business Objects、SAP Lumira、IBM Cognos、JasperSoft、Microsoft BI Platform、Tableau、Oracle Business Intelligence EnterpriseEditionなどがあります。
市場で入手可能な人気のあるETLツールは次のとおりです。
- Informatica-パワーセンター
- IBM-Websphere DataStage（旧称Ascential DataStage）
- SAP-Business Objects Data Services BODS
- IBM-Cognos Data Manager（旧称Cognos Decision Stream）
- Microsoft-SQL Server Integration Services SSIS
- Oracle-Data Integrator ODI（旧称Sunopsis Data Conductor）
- SAS-データ統合スタジオ
- Oracle-Warehouse Builder
- ABInitio
- オープンソースのクローバーETL
ステージング領域は、データソースとデータウェアハウス/データマートシステムの間に位置する中間領域です。ステージング領域は多くの利点を提供するように設計できますが、それらを使用する主な動機は、ETLプロセスの効率を高め、データの整合性を確保し、データ品質の運用をサポートすることです。
データウェアハウジングは、データマイニングと比較してより広い概念です。データマイニングには、データから隠された情報を抽出し、将来の予測のためにそれを解釈することが含まれます。対照的に、データウェアハウジングには、詳細レポートやアドホックレポートを生成するための分析レポート、インタラクティブなダッシュボードやチャートを生成するための情報処理などの操作が含まれます。
OLTPは、一般にリレーショナルデータベースであり、日常のトランザクションを管理するために使用されるオンライントランザクション処理システムの略です。
OLAPは、一般に多次元システムであり、データウェアハウスとも呼ばれるオンライン分析処理システムの略です。
会社が自社の製品を顧客に販売するとします。すべての販売は社内で行われるファクトであり、ファクトテーブルはこれらのファクトを記録するために使用されます。各ファクトテーブルには、ファクトテーブルをディメンションテーブルおよびメジャー/ファクトに結合するための主キーが格納されます。
Example − Fact_Units
| Cust_ID | Prod_Id | Time_Id | 販売台数 |
|---|---|---|---|
| 101 | 24 | 1 | 25 |
| 102 | 25 | 2 | 15 |
| 103 | 26 | 3 | 30 |
ディメンションテーブルには、ファクトテーブル内のオブジェクトを説明する属性またはディメンションが格納されます。これは、ファクトテーブルのコンパニオンテーブルのセットです。
Example − Dim_Customer
| Cust_id | Cust_Name | 性別 |
|---|---|---|
| 101 | ジェイソン | M |
| 102 | アンナ | F |
データマートはデータウェアハウスの単純な形式であり、単一の機能領域に焦点を合わせています。通常、データはいくつかのソースからのみ取得されます。
Example −組織内には、財務、マーケティング、人事、および特定の機能に関連するデータを格納するその他の個々の部門用のデータマートが存在する場合があります。
集計関数は、単一の列の複数の行をグループ化して、より重要な測定値を形成するために使用されます。これらは、集約されたテーブルをデータウェアハウスに保存する際のパフォーマンスの最適化にも使用されます。
一般的な集計関数は次のとおりです。
| MIN | 指定された列の最小値を返します |
| MAX | 指定された列の最大値を返します |
| 和 | 指定された列の数値の合計を返します |
| AVG | 指定された列の平均値を返します |
| カウント | 指定された列の値の総数を返します |
| カウント（*） | テーブルの行数を返します |
Example
SELECT AVG(salary)
FROM employee
WHERE title = 'developer';データ定義言語（DDL）ステートメントは、データベース構造またはスキーマを定義するために使用されます。
Examples −
CREATE −データベースにオブジェクトを作成する
ALTER −データベースの構造を変更します
データ操作言語（DML）ステートメントは、データベース内のデータを操作するために使用されます。
Examples −
SELECT −データベースからデータを取得します
INSERT −データをテーブルに挿入します
UPDATE −テーブル内の既存のデータを更新します
DELETE −テーブルからすべてのレコードを削除し、レコード用のスペースを残します
データ制御言語（DCL）ステートメントは、データベースオブジェクトへのアクセスを制御するために使用されます。
Examples −
GRANT −データベースへのユーザーのアクセス権限を付与します
REVOKE −GRANTコマンドで指定されたアクセス権を撤回します
演算子は、SQLステートメントで条件を指定し、ステートメントで複数の条件の接続詞として機能するために使用されます。一般的な演算子の種類は次のとおりです。
- 算術演算子
- 比較/関係演算子
- 論理演算子
- 演算子の設定
- 条件を否定するために使用される演算子
SQLの一般的な集合演算子は次のとおりです。
- UNION
- UNION ALL
- INTERSECT
- MINUS
交差操作は、2つのSELECTステートメントを組み合わせるために使用されますが、両方のSELECTステートメントから共通のレコードのみを返します。Intersectの場合、列の数とデータ型は同じである必要があります。MySQLはINTERSECT演算子をサポートしていません。交差クエリは次のようになります-
select * from First
INTERSECT
select * from secondマイナス演算は、2つのSelectステートメントの結果を組み合わせて、最初の結果セットに属する結果のみを返します。マイナスクエリは次のようになります-
select * from First
MINUS
select * from secondソースマイナスターゲットとターゲットマイナスソースを実行し、マイナスクエリが値を返す場合は、行が一致しない場合と見なす必要があります。
マイナスクエリが値を返し、交差するカウントがソースカウントまたはターゲットテーブルよりも小さい場合、ソーステーブルとターゲットテーブルに重複する行が含まれます。
Group-by 句はで使用されます select 同様のタイプのデータを収集するステートメント。 HAVING に非常に似ています WHERE ただし、その中のステートメントは集合的な性質のものです。
Syntax −
SELECT dept_no, count ( 1 ) FROM employee GROUP BY dept_no;
SELECT dept_no, count ( 1 ) FROM employee GROUP BY dept_no HAVING COUNT( 1 ) > 1;Example −従業員テーブル
| Country | Salary |
| インド | 3000 |
| 我ら | 2500 |
| インド | 500 |
| 我ら | 1500 |
Group by Country
| Country | Salary |
| インド | 3000 |
| インド | 500 |
| 我ら | 2500 |
| 我ら | 1500 |
ETLテストは、データが本番データウェアハウスシステムに移動される前に実行されます。これは、テーブルバランシングまたは生産調整とも呼ばれます。
ETLテストの主な目的は、分析レポート用のデータを処理する前に発生するデータの欠陥と一般的なエラーを特定して軽減することです。
次の表は、データベースとETLのテストの主な機能とそれらの比較を示しています。
| 関数 | データベーステスト | ETLテスト |
|---|---|---|
| 主な目標 | データの検証と統合 | BIレポートのデータ抽出、変換、および読み込み |
| 該当するシステム | ビジネスフローが発生するトランザクションシステム | 履歴データを含み、ビジネスフロー環境にないシステム |
| 市場に出回っている一般的なツール | QTP、Seleniumなど。 | QuerySurge、Informaticaなど。 |
| ビジネスニーズ | 複数のアプリケーションからのデータを統合するために使用され、深刻な影響を及ぼします。 | 分析レポート、情報、および予測に使用されます。 |
| モデリング | ER法 | 多次元 |
| データベースタイプ | 通常、OLTPシステムで使用されます | OLAPシステムに適用されます |
| データ・タイプ | より多くの結合を持つ正規化されたデータ | 結合が少なく、インデックスと集計が多い非正規化データ。 |
ETLテストは、その機能に基づいて次のカテゴリに分類できます-
Source to Target Count Testing −ソースシステムとターゲットシステムのレコード数の照合が含まれます。
Source to Target Data Testing−ソースシステムとターゲットシステム間のデータ検証が含まれます。また、ターゲットシステムでのデータ統合としきい値チェックおよび重複データチェックも含まれます。
Data Mapping or Transformation Testing−ソースシステムとターゲットシステムのオブジェクトのマッピングを確認します。また、ターゲットシステムのデータの機能をチェックすることも含まれます。
End-User Testing−レポート内のデータが期待どおりであるかどうかを確認するために、エンドユーザー向けのレポートを生成する必要があります。これには、レポートの偏差を見つけ、レポート検証のためにターゲットシステムのデータをクロスチェックすることが含まれます。
Retesting −ターゲットシステムのデータのバグと欠陥を修正し、データ検証のためにレポートを再実行する必要があります。
System Integration Testing −個々のシステムをすべてテストし、後で結果を組み合わせて偏差があるかどうかを確認します。
ETLプロセス中のデータ損失。
データが正しくない、不完全である、または重複している。
DWシステムには履歴データが含まれているため、データ量が多すぎて非常に複雑であるため、ターゲットシステムでETLテストを実行できません。
ETLテスターには通常、ETLツールでジョブスケジュールを表示するためのアクセス権が提供されていません。レポートやレポート内のデータの最終的なレイアウトを確認するために、BIレポートツールにアクセスすることはほとんどありません。
データ量が多すぎて複雑なため、テストケースの生成と構築が困難です。
ETLテスターは通常、エンドユーザーレポートの要件と情報のビジネスフローについての考えを持っていません。
ETLテストには、ターゲットシステムでのデータ検証のためのさまざまな複雑なSQLの概念が含まれます。
テスターには、ソースからターゲットへのマッピング情報が提供されない場合があります。
不安定なテスト環境では、開発とプロセスのテストが遅れます。
ETLテスターの主な責任は次のとおりです。
ソースシステムのテーブルの確認-カウントチェック、データタイプチェック、キーが欠落していない、データが重複しています。
データをロードする前に変換ロジックを適用する：データしきい値の検証、代理kyチェックなど。
ステージング領域からターゲットシステムへのデータのロード：集計値と計算されたメジャー、キーフィールドが欠落していない、ターゲットテーブルのカウントチェック、BIレポートの検証など。
ETLツールとそのコンポーネントのテスト、テストケース-テストプラン、テストケース、テストETLツールとその機能、テストDWシステムなどを作成、設計、実行します。
変換は、データを生成、変更、または渡す一連のルールです。変換には、アクティブとパッシブの2つのタイプがあります。
アクティブな変換では、変換が発生すると、出力として作成される行数を変更できます。これは、受動的変換中には発生しません。情報は、入力として与えられたのと同じ番号を通過します。
パーティショニングとは、データストアの領域を部分的に分割することです。これは通常、トランザクションのパフォーマンスを向上させるために行われます。
DWシステムのサイズが大きい場合、データの検索に時間がかかります。ストレージスペースを分割すると、データをより簡単かつ迅速に検索して分析できます。
分割には、ラウンドロビン分割とハッシュ分割の2つのタイプがあります。
ラウンドロビンパーティショニングでは、データがすべてのパーティションに均等に分散されるため、各パーティションの行数は比較的同じです。ハッシュパーティション分割とは、サーバーがデータをグループ化するためのパーティションキーを作成するためにハッシュ関数を使用する場合です。
マップレットは変換ルールを定義します。
セッションは、データがソースシステムからターゲットシステムに移動されたときにデータを指示するように定義されています。
ワークフローは、タスクの実行についてサーバーに指示する一連の命令です。
マッピングとは、ソースから宛先へのデータの移動です。
ルックアップトランスフォーメーションを使用すると、マッピングドキュメントで定義されていないリレーショナルテーブルのデータにアクセスできます。ゆっくりと変化するディメンションテーブルを更新して、レコードがターゲットにすでに存在するかどうかを判断できます。
サロゲートキーは、シーケンスによって生成された意味のない番号を持ち、行を一意に識別するためのものです。ユーザーやアプリケーションには表示されません。候補キーとも呼ばれます。
サロゲートキーには、意味のないシーケンス生成の番号があります。これは、行を一意に識別することを目的としています。
主キーは、行を一意に識別するために使用されます。ユーザーに表示され、必要に応じて変更できます。
このような場合、チェックサム方式を適用できます。ソースシステムとターゲットシステムのレコード数を確認することから始めることができます。合計を選択し、情報を比較します。
このテストでは、テスターがデータの範囲を検証します。ターゲットシステムのすべてのしきい値をチェックして、期待どおりの結果であることを確認します。
Example −年齢属性の値は100を超えてはなりません。日付列DD / MM / YYで、月フィールドの値は12を超えてはなりません。
Select Cust_Id, Cust_NAME, Quantity, COUNT (*)
FROM Customer GROUP BY Cust_Id, Cust_NAME, Quantity HAVING COUNT (*) >1;主キーが定義されていない場合、重複する値が表示される可能性があります。
マッピングが正しくない場合や、ソースシステムからターゲットシステムにデータを転送する際の手動エラーが原因で、データの重複が発生する場合もあります。
回帰テストとは、データ変換と集計のルールに変更を加えて新しい機能を追加し、テスターが新しいエラーを見つけられるようにすることです。回帰テストで得られるデータに現れるバグは、回帰と呼ばれます。
3つのアプローチは、トップダウン、ボトムアップ、およびハイブリッドです。
最も一般的なETLテストシナリオは次のとおりです。
- 構造検証
- マッピングドキュメントの検証
- 制約を検証する
- データの整合性チェック
- データの完全性の検証
- データの正確性の検証
- データ変換の検証
- データ品質の検証
- ヌル検証
- 重複検証
- 日付検証チェック
- マイナスクエリを使用した完全なデータ検証
- その他のテストシナリオ
- データクリーニング
データパージは、データウェアハウスからデータを削除するプロセスです。null値や余分なスペースを含む行などのジャンクデータを削除します。
外観上のバグは、アプリケーションのGUIに関連しています。これは、フォントスタイル、フォントサイズ、色、配置、スペルミス、ナビゲーションなどに関連している可能性があります。
これは、境界値分析関連のバグと呼ばれます。
これを行うには、マッピング変数とフィルター処理された変換を作成します。必要な特別にソートされたレコードを取得するために、シーケンスを生成する必要がある場合があります。
Value comparison−ソースシステムとターゲットシステムのデータを、変換を最小限に抑えて、または変換せずに比較する必要があります。これは、Informaticaのソース修飾子変換などのさまざまなETLテストツールを使用して実行できます。
重要なデータ列は、ソースシステムとターゲットシステムの個別の値を比較することで確認できます。
マイナスステートメントと交差ステートメントを使用して、データの完全性の検証を実行できます。ソースマイナスターゲットとターゲットマイナスソースを実行し、マイナスクエリが値を返す場合、それは行の不一致の兆候です。
マイナスクエリが値を返し、交差するカウントがソースカウントまたはターゲットテーブルよりも小さい場合、重複する行が存在します。
Shortcut Transformation共有フォルダーで使用可能なオブジェクトへの参照です。これらの参照は、さまざまなプロジェクトまたは環境間で共有されるさまざまなソースおよびターゲットに一般的に使用されます。
リポジトリマネージャでは、「共有」ステータスを割り当てることでショートカットが作成されます。後で、オブジェクトをこのフォルダから別のフォルダにドラッグできます。このプロセスにより、オブジェクトの単一の制御ポイントが可能になり、複数のプロジェクトのローカルフォルダーにすべてのインポートソースとターゲットが含まれるわけではありません。
Reusable Transformation フォルダに対してローカルです。 Example−ウェアハウスの顧客IDを割り当てるための再利用可能なシーケンスジェネレータ。複数のソースシステムから顧客の詳細をロードし、新しい各ソースキーに一意のIDを割り当てると便利です。
単一のテーブルをそれ自体に結合する場合、それは自己結合と呼ばれます。
データベースの正規化は、データの冗長性を最小限に抑えるために、リレーショナルデータベースの属性とテーブルを整理するプロセスです。
正規化では、情報を失うことなく、テーブルを冗長性の低い（より小さな）テーブルに分解します。
ファクトのないファクトテーブルは、メジャーがないファクトテーブルです。それは本質的に次元の共通部分です。ファクトレステーブルには2つのタイプがあります。1つはイベントをキャプチャするためのもので、もう1つは条件を記述するためのものです。
ゆっくりと変化するディメンションとは、時間の経過とともに変化する属性の値を指します。SCDには、タイプ1、タイプ2、およびタイプ3の3つのタイプがあります。