CSV ファイルをはじめとするシンプルなフォーマットを Power Query で読み込むとき、どのようなことが起きているか観察するとよいと思うのです。Power Query で変形加工を行うということはどういうことなのかの理解が捗るはずだ。
Power BI Desktop と Excel いずれでもモミモミして観察したけど、顕著な状況が観察できた Excel で話を進める。ログ量を減らすためにわずか 2,000行 で確かめているので、クエリ評価のパフォーマンスまでを確認しているのではない。
思ったこと🙄
速く処理をさせるという目標ではなく、処理が遅くならないことを目標としたほうが健全かなと思う。もちろんこれらには、間違ったロジックやフローを選択していないことが含まれるよね。CSVだから遅いねということはあるけれども、シンプルなフォーマットだからできることもあるのだ。
定義したクエリやステップまたはフローを解釈し、それらから必要な所作としてファイルが読み込まれるのだから、思ってた通りの処理だけにとどまっているのかぐらいの確認ができるよう準備しておきたいものである。列挙したパターンの結果はごく一部ね。
意図的にファイルを読み込む処理に変更を仕掛けることはとても難しいだろうねと思っているのだけど、そんなこと考えている余裕があるなら、ファイルデータベースからもっと高速な処理が期待できるデータソースやフローに変換したほうがよいのでは？と。
ファイルの読み込みは多くの場合で高速なはずだから、
- ファイルの読み込みは1回だけとは限らず複数回行われることがある。
- ファイルの一部を読み込んだり、全体を読み込んだりもする。
ということだと思うね。で、よく観察し理解することでトラブルシューティングに生かせるのだね。
プロセスモニタで観察
2,000行10列のテーブルを表現するCSVファイル読み込みをプロセスモニタで観察。
let
Source = Csv.Document(
File.Contents("D:\Data\R2000C10.csv"),
[Delimiter=",", Columns=10, QuoteStyle=QuoteStyle.None]
),
PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
PromotedHeaders
このときのログはこの通りで、先頭から部分的に読み込んでいること含め複数読み込みされていることがわかる。このうちいくつかの読み込みについて確認。
列数を指定しない
let
Source = Csv.Document(
File.Contents("D:\Data\R2000C10.csv"),
[Delimiter=",", Columns=null, QuoteStyle=QuoteStyle.None]
),
PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
PromotedHeaders
先頭 4K byteの読み込みが増えた。ごくわずかなイベントではあるからそれ自体は気にすることはないのだけど、列数を指定しないことで発生する事象についてよーく考えておくべきだ。Power BI service によるデータセット更新が失敗する原因になりますからね。
オプション [Background Data] をOFF にする
let
Source = Csv.Document(
File.Contents("D:\Data\R2000C10.csv"),
[Delimiter=",", Columns=null, QuoteStyle=QuoteStyle.None]
),
PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
PromotedHeaders
クエリの評価結果のための読み込みを終えたあと、Power Query エディタのプレビュー用途で追加読み込み。オプションをOFFにすることで追加読み込むをしなくなる。ただし、Power Query エディタでの作業が引き続き行われるのであれば ON のままが都合がよいことが多いし、もうPower Query エディタ 上での作業がないのであれば OFF にしたらよい。
1行目をヘッダとして使用しない
let
Source = Csv.Document(
File.Contents("D:\Data\R2000C10.csv"),
[Delimiter=",", Columns=10, QuoteStyle=QuoteStyle.None]
)
in
Source
先頭 4K bytes の読み込みがヘッダ用の読み込みなのだろう。わずかなイベントなので気にすることはない。
先頭行の選択
let
Source = Csv.Document(
File.Contents("D:\Data\R2000C10.csv"),
[Delimiter=",", Columns=10, QuoteStyle=QuoteStyle.None]
),
PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
KeptFirstRows = Table.FirstN(PromotedHeaders,1000)
in
KeptFirstRows
ファイル終端まで読み込まれることはない。
最終行のみ選択
let
Source = Csv.Document(
File.Contents("D:\Data\R2000C10.csv"),
[Delimiter=",", Columns=10, QuoteStyle=QuoteStyle.None]
),
PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
KeptLastRows = Table.LastN(PromotedHeaders, 1)
in
KeptLastRows
ファイル終端まで読み込むことで最終行を取得
行フィルタの適用
let
Source = Csv.Document(
File.Contents("D:\Data\R2000C10.csv"),
[Delimiter=",", Columns=10, QuoteStyle=QuoteStyle.None]
),
PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
FilteredRows = Table.SelectRows(PromotedHeaders, each ([Column1] = "1"))
in
FilteredRows
全行に対し検査が必要だからすべて読み込みが必要になる。なので、可能であれば先頭行のみの読み込み( Table.FirstN )を使ったほうがよいときがある。
テーブルのソート
let
Source = Csv.Document(
File.Contents("D:\Data\R2000C10.csv"),
[Delimiter=",", Columns=10, QuoteStyle=QuoteStyle.None]
),
PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
SortedRows = Table.Sort(PromotedHeaders,{{"Column1", Order.Ascending}})
in
SortedRows
ファイル終端までの読み込みが2回発生する。
2回読み込むからソートが遅いのではなく、2回読み込ませてもソートが遅いのだ。
列の選択
let
Source = Csv.Document(
File.Contents("D:\Data\R2000C10.csv"),
[Delimiter=",", Columns=10, QuoteStyle=QuoteStyle.None]
),
PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
RemovedOtherColumns = Table.SelectColumns(PromotedHeaders,{"Column1"})
in
RemovedOtherColumns
カラム プルーニングをサポートしているわけがない。
その他