Openhack resources

# Openhack resources

備忘もかねて
[Openhack for Lakehouse](https://github.com/microsoft/openhack-for-lakehouse-japanese.git) で参考となる情報をまとめます。

## ソースコード

- clone用：https://github.com/microsoft/openhack-for-lakehouse-japanese.git
- dbc用：https://github.com/microsoft/openhack-for-lakehouse-japanese/releases/tag/v1.1.1
- 旧バージョン（3日間版＋tip増の大容量版）：https://github.com/microsoft/openhack-for-lakehouse-japanese/releases/tag/v1.0.0

## 共通

### Databricks 無償版（コミュニティプラン）

- https://qiita.com/taka_yayoi/items/f00ddb376b27936a5558
- https://www.databricks.com/jp/blog/2021/04/22/get-started-with-databricks-community-edition-jp.html

### 学習

- databricks 社の教育サイト：https://learn.microsoft.com/ja-jp/training/modules/use-apache-spark-azure-databricks/
- msdocs: https://learn.microsoft.com/ja-jp/training/modules/use-apache-spark-azure-databricks/ 
- チートシート：https://pages.databricks.com/rs/094-YMS-629/images/Delta-Lake-cheat-sheet.pdf

### Community 

- [dllab community](https://dllab.connpass.com/)
- [jssug community](https://sqlserver.connpass.com/)
- [MS techcommunity blog](https://techcommunity.microsoft.com/t5/custom/page/page-id/Blogs)
- [しばやん雑記](https://blog.shibayan.jp/)
- [SE の雑記](https://blog.engineer-memo.com/)
- [ブチザッキ](https://blog.azure.moe/about/)

## Day1

### git連携

- https://qiita.com/taka_yayoi/items/b89f199ff0d3a4c16140
- https://learn.microsoft.com/ja-jp/azure/databricks/repos/

### エディタ：
- [IDE機能](https://qiita.com/taka_yayoi/items/b6adb35a48e77b4962c8)
- [dbutils](https://learn.microsoft.com/ja-jp/azure/databricks/dev-tools/databricks-utils)

### ファイル入出力、連携
- ms docs:https://learn.microsoft.com/ja-jp/azure/databricks/getting-started/dataframes-python
- https://qiita.com/ktmrmshk/items/54ce2d6f274a67b2e54c
- https://sparkbyexamples.com/spark/spark-read-options/#:~:text=Spark%20provides%20several%20read%20options%20that%20help%20you,DataFrame%20or%20Dataset%20depending%20on%20the%20API%20used.
- スキーマの拡張：https://www.databricks.com/jp/blog/2019/09/24/diving-into-delta-lake-schema-enforcement-evolution.html
- [pysparkでのjsonの取り扱い例](https://qiita.com/ryoma-nagata/items/4cd9a28d7fd11e6cec40#synapse-spark-%E3%81%AB%E3%82%88%E3%82%8B%E3%83%87%E3%83%BC%E3%82%BF%E3%82%A8%E3%83%B3%E3%82%B8%E3%83%8B%E3%82%A2%E3%83%AA%E3%83%B3%E3%82%B0)
- [jsonデータのネスト項目の取得](https://learn.microsoft.com/ja-jp/azure/databricks/sql/language-manual/sql-ref-json-path-expression#extract-nested-fields)
- https://learn.microsoft.com/ja-jp/azure/databricks/sql/language-manual/delta-merge-into
- [Power query](https://support.microsoft.com/ja-jp/office/%E7%B5%B1%E8%A8%88%E6%83%85%E5%A0%B1%E3%82%92%E8%A1%A8%E7%A4%BA%E3%81%99%E3%82%8B%E3%83%97%E3%83%AD%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB-%E3%83%87%E3%83%BC%E3%82%BF-power-query-79616636-43aa-428f-b14b-f9c5c060f6b2#:~:text=By%20default%2C%20Power%20Query%20profiles%20data%20over%20the,or%20Column%20profiling%20based%20on%20entire%20data%20set.)
- [Power query](https://learn.microsoft.com/ja-jp/power-query/data-profiling-tools)
- [データマートの処理方法 CTAS=>全件洗い替え　たいていはこれでOK。データ量が多いときにバッチ時間が足りないケースでは別の方法を採用する](https://qiita.com/abe_masanori/items/1835ddbf1fa2e13d3514)
- [【トリビアのDelta Lake】#4 Spark DataFrameの変換チートシートを作りました - Qiita](https://qiita.com/yuulian/items/836fad4aab6c7cf19ed1)

### サンプル

- delta lake : https://docs.delta.io/latest/quick-start.html
- delta lake tutorial https://learn.microsoft.com/ja-jp/azure/databricks/delta/tutorial
- datasets https://qiita.com/ryoma-nagata/items/5f34c8f40cbced373ab0

### vnet アーキテクチャ

- datalake へのアクセス　https://qiita.com/ryoma-nagata/items/66c48dd2a86956c0d00d#vnet-%E3%82%A4%E3%83%B3%E3%82%B8%E3%82%A7%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3
- ウェビナー https://pages.databricks.com/20230419_adb_learning_series_v3_Registration.html?utm_source=msft&utm_medium=partner&utm_campaign=7018y000001ff6mqac
 

### autoLoader COpyinto
- avalible now: https://spark.apache.org/docs/latest/api/python/reference/pyspark.ss/api/pyspark.sql.streaming.DataStreamWriter.trigger.html
- copyinto と autoloader  https://qiita.com/ryoma-nagata/items/74e1bd9ebaf0413c9fd6

### python api 

- python api https://docs.delta.io/latest/api/python/index.html
- delta lake oss  https://docs.delta.io/latest/delta-apidoc.html

### クラスター、ジョブ、プール
- github best plactice https://github.com/Azure/AzureDatabricksBestPractices/blob/master/toc.md#do-not-store-any-production-data-in-default-dbfs-folders
- mslean https://learn.microsoft.com/ja-jp/azure/databricks/clusters/cluster-config-best-practices
- job https://learn.microsoft.com/ja-jp/azure/databricks/workflows/jobs/jobs#best-practices
- price https://azure.microsoft.com/ja-jp/pricing/details/databricks/
- pool https://learn.microsoft.com/ja-jp/azure/databricks/clusters/pool-best-practices
- 

### SQLWH
- 同時実行 https://learn.microsoft.com/ja-jp/azure/databricks/sql/admin/sql-endpoints#queueing-and-autoscaling
- [Databricks SQL WHのクラスタ内訳](https://learn.microsoft.com/ja-jp/azure/databricks/sql/admin/sql-endpoints#cluster-size)

### other
- pprint https://note.nkmk.me/python-pprint-pretty-print/
- [job api からデータ取得するscalaサンプル](https://learn.microsoft.com/en-us/azure/databricks/kb/jobs/job-run-dash)

## Day2

### ML runtime

- ビルトインライブラリ https://learn.microsoft.com/en-us/azure/databricks/release-notes/runtime/12.2ml

### mlflow

- [mlflow AzureML](https://qiita.com/ShuntaIto/items/9daae0f87dbeca48ccdc)
- [mlflow databricks ](https://qiita.com/ShuntaIto/items/98676a4e85d1cf5af52e)
- [mlops](https://speakerdeck.com/shisyu_gaku/step-by-step-mlops-and-microsoft-products)
- [mlflow autolog](https://learn.microsoft.com/ja-jp/azure/databricks/mlflow/databricks-autologging)
- [hyperopt mlflow](https://learn.microsoft.com/ja-jp/azure/databricks/machine-learning/automl-hyperparam-tuning/hyperopt-concepts)

### feature store

- sample https://learn.microsoft.com/en-us/azure/databricks/machine-learning/feature-store/example-notebooks

### pandas on spark

- pandas on spark https://learn.microsoft.com/en-us/azure/databricks/pandas/pandas-on-spark
- 


## openhack再現の仕方

前提
- データセットのダウンロード
    - https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce?select=olist_customers_dataset.csv
- datarbricks環境の作成

管理者設定でDBFSファイルブラウザを有効にする

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/9ac299dc-7f94-b307-b014-b6e626439637.png)

データエクスプローラからDBFSを閲覧

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/a713eb49-0d75-d7bf-aa05-7510d3547f83.png)


右クリックなどでフォルダを作成し、「/FileStore/db_hackathon4lakehouse_2022/datasource」フォルダにデータセットをすべてアップする
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/20bf4177-a147-7b48-af8f-767a7ea9f30a.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/3e406471-59d9-874f-4098-30bfc5418ded.png)