pyspark-mysql.sh

?

!apt-get install openjdk-8-jdk-headless -qq > /dev/null
!wget -q https://downloads.apache.org/spark/spark-2.4.7/spark-2.4.7-bin-hadoop2.7.tgz
!tar xf spark-2.4.7-bin-hadoop2.7.tgz

!pip install -q findspark
!pip install -q pyarrow

!wget https://github.com/welly87/spark-load/raw/master/mysql-connector-java-8.0.14.jar
!mv /content/mysql-connector-java-8.0.14.jar /content/spark-2.4.7-bin-hadoop2.7/jars/mysql-connector-java-8.0.14.jar

import os
os.environ["JAVA_HOME"] = "/usr/lib/jvm/java-8-openjdk-amd64"
os.environ["SPARK_HOME"] = "/content/spark-2.4.7-bin-hadoop2.7"

driver_path = "/content/mysql-connector-java-8.0.14.jar" 

os.environ['PYSPARK_SUBMIT_ARGS'] = f'pyspark-shell --driver-class-path {driver_path} --jars {driver_path}'

import findspark
findspark.init("spark-2.4.7-bin-hadoop2.7")

from pyspark.sql import SparkSession

spark = SparkSession \
    .builder \
    .appName("Bea Cukai - PySpark/Kudu") \
    .config('spark.driver.bindAddress','localhost') \
    .config('spark.driver.allowMultipleContexts','true') \
    .config('spark.sql.execution.arrow.pyspark.enabled', 'true') \
    .getOrCreate()

https://relational.fit.cvut.cz/assets/img/datasets-generated/ccs.svg

jdbcDF = spark.read \
  .format("jdbc") \
  .option("url", "jdbc:mysql://relational.fit.cvut.cz/ccs?serverTimezone=UTC") \
  .option("driver", "com.mysql.jdbc.Driver") \
  .option("dbtable", "transactions_1k") \
  .option("user", "guest") \
  .option("password", "relational") \
  .load()

jdbcDF.createOrReplaceTempView("transactions")
trans = spark.sql("select * from transactions limit where Amount > 20")
trans.count()

tdf = trans.select("*").toPandas()
tdf.head()

https://github.com/welly87/spark-load/blob/master/load_finance_data.scala

SELECT * FROM welly.transactions_1k;

CREATE EXTERNAL TABLE 001_andi.transactions_1k
STORED AS KUDU
TBLPROPERTIES (
  'kudu.table_name' = 'transactions_1k'
);

CREATE EXTERNAL TABLE welly.transactions_1k
STORED AS KUDU
TBLPROPERTIES (
  'kudu.table_name' = 'transactions_1k'
);

CREATE EXTERNAL TABLE welly.customers
STORED AS KUDU
TBLPROPERTIES (
  'kudu.table_name' = 'customers'
);

CREATE EXTERNAL TABLE welly.gasstations
STORED AS KUDU
TBLPROPERTIES (
  'kudu.table_name' = 'gasstations'
);

CREATE EXTERNAL TABLE welly.products
STORED AS KUDU
TBLPROPERTIES (
  'kudu.table_name' = 'products'
);

https://github.com/hackathonBI/CCS

https://github.com/hackathonBI/CCS/blob/master/Tasks.md

!pip install impyla

from impala.dbapi import connect
from impala.util import as_pandas

conn = connect(host='178.128.112.105', port=21050) 
cursor = conn.cursor()
cursor.execute('SELECT * FROM welly.transactions_1k LIMIT 100')
df = as_pandas(cursor)

df.head()