Databricks のサンプルデータセットの１つである TPC-H のデータからSpark データフレームを作成する方法

## 概要

Databricks のサンプルデータセットの１つである TPC-H のデータから、Spark データフレームを作成する方法を共有します。


```python
# ストレージのファイルを表示する
display(dbutils.fs.ls('dbfs:/databricks-datasets/tpch/data-001'))

path_to_md = "dbfs:/databricks-datasets/tpch/README.md"
displayHTML("<pre>" + dbutils.fs.head(path_to_md) + "</pre>")

path_to_md = "dbfs:/databricks-datasets/tpch/data-001/README.md"
displayHTML("<pre>" + dbutils.fs.head(path_to_md) + "</pre>")
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/a39359cd-644a-62b5-e756-02c3a74ee140.png)

TPC-H がどういったものであるかは、下記の記事で整理しております。

[データベースの性能検証に利用されるTPC-HとTPC-DSに関するざっくりとした整理 - Qiita](https://qiita.com/manabian/items/93d9330cdb607383c2b9)

## データフレームの作成方法

### customer

```python
 
filepath = "dbfs:/databricks-datasets/tpch/data-001/customer/customer.tbl"
 
schema = """
  c_custkey long,
  c_name string,
  c_address string,
  c_nationkey long,
  c_phone string,
  c_acctbal decimal(12, 2),
  c_mktsegment string,
  c_comment string
"""
   
df = (spark
       .read
       .format("csv")
       .schema(schema)
       .option("sep", "|")
       .load(filepath)
    )
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/587b6061-631b-e303-518b-bcf3b76f0e2f.png)


### lineitem

```python
filepath = "dbfs:/databricks-datasets/tpch/data-001/lineitem/lineitem.tbl"
 
schema = """
  l_orderkey    integer ,
  l_partkey     integer ,
  l_suppkey     integer ,
  l_linenumber  integer ,
  l_quantity    decimal(15,2) ,
  l_extendedprice  decimal(15,2) ,
  l_discount    decimal(15,2) ,
  l_tax         decimal(15,2) ,
  l_returnflag  string ,
  l_linestatus  string ,
  l_shipdate    date ,
  l_commitdate  date ,
  l_receiptdate date ,
  l_shipinstruct string ,
  l_shipmode     string ,
  l_comment      string
"""
   
df = (spark
       .read
       .format("csv")
       .schema(schema)
       .option("sep", "|")
       .load(filepath)
    )
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/a222a251-97df-7b0e-827f-3608a2b6c3ef.png)



### nation

```python
filepath = "dbfs:/databricks-datasets/tpch/data-001/nation/nation.tbl"
 
schema = """
  n_nationkey  integer
  ,n_name       string
  ,n_regionkey  integer 
  ,n_comment    string
"""
   
df = (spark
       .read
       .format("csv")
       .schema(schema)
       .option("inferSchema", "True")
       .option("sep", "|")
       .load(filepath)
    )
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/58212392-337f-9661-b2f5-51d73bb977b2.png)


### orders

```python
 
filepath = "dbfs:/databricks-datasets/tpch/data-001/orders/orders.tbl"
 
schema = """
    o_orderkey long,
    o_custkey long,
    o_orderstatus string,
    o_totalprice decimal(12, 2),
    o_orderdate date,
    o_orderpriority string,
    o_clerk string,
    o_shippriority int,
    o_comment string
"""
   
df = (spark
       .read
       .format("csv")
       .schema(schema)
       .option("sep", "|")
       .load(filepath)
    )
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/21fe1be8-de63-8338-abe8-42f026ea76f2.png)


### part

```python
filepath = "dbfs:/databricks-datasets/tpch/data-001/part/part.tbl"
 
schema = """
  p_partkey long,
  p_name string,
  p_mfgr string,
  p_brand string,
  p_type string,
  p_size int,
  p_container string,
  p_retailprice decimal(12, 2),
  p_comment string
"""
   
df = (spark
       .read
       .format("csv")
       .schema(schema)
       .option("sep", "|")
       .load(filepath)
    )
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/79fd6324-b554-39f5-99b7-1b36a360e826.png)


### partsupp

```python
filepath = "dbfs:/databricks-datasets/tpch/data-001/partsupp/partsupp.tbl"
 
schema = """
  ps_partkey long,
  ps_suppkey long,
  ps_availqty int,
  ps_supplycost decimal(12, 2),
  ps_comment string
"""
   
df = (spark
       .read
       .format("csv")
       .schema(schema)
       .option("sep", "|")
       .load(filepath)
    )
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/bc63a990-122a-a9cc-4ca8-be5b9057fbb6.png)


### region

```python
filepath = "dbfs:/databricks-datasets/tpch/data-001/region/region.tbl"
 
schema = """
    r_regionkey long,
    r_name string,
    r_comment string
"""
   
df = (spark
       .read
       .format("csv")
       .schema(schema)
       .option("sep", "|")
       .load(filepath)
    )
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/e2179140-25dd-36ce-cc3e-ae70b07f97ce.png)

### supplier

```python
filepath = "dbfs:/databricks-datasets/tpch/data-001/supplier/supplier.tbl"
 
schema = """
  s_suppkey long,
  s_name string,
  s_address string,
  s_nationkey long,
  s_phone string,
  s_acctbal decimal(12, 2),
  s_comment string
"""
   
df = (spark
       .read
       .format("csv")
       .schema(schema)
       .option("sep", "|")
       .load(filepath)
    )
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/2eb7b5b0-e071-416c-782e-700fcfb689b6.png)