# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

from MODAK_driver import MODAK_driver

driver = MODAK_driver()

repo_dir = '/Users/ksivalinga/Documents/sodalite/iac-model-repo/db/test-iac-model'

df = driver.applySQL("CREATE DATABASE `iac_model`")

df = driver.applySQL("CREATE DATABASE `test_iac_model`")

df = driver.applySQL('show databases')
df.show()

df.count()

df = driver.applySQL('use test_iac_model')

df = driver.applySQL('show tables')
df.show()

driver.applySQL('drop table infrastructure')
CREATE_INFRA_TABLE = "create external table          \
        infrastructure(infra_id  int,                \
        name string,                                 \
        num_nodes int,                               \
        is_active boolean,                           \
        description string)                          \
        stored as PARQUET location                   \
        '{}/infrastructure'"
driver.applySQL(CREATE_INFRA_TABLE.format(repo_dir))

df = driver.applySQL('desc infrastructure')
df.show()


df = driver.applySQL("insert into infrastructure select 1 as infra_id,'test' as name,2 as num_nodes,\
TRUE as is_active, 'test' as description")
df.show()

driver.applySQL('drop table queue')
CREATE_QUEUE_TABLE = "create external table          \
        queue(queue_id  int,                         \
        name string,                                 \
        num_nodes int,                               \
        is_active boolean,                           \
        node_spec string,                            \
        description string,                          \
        infra_id int)                                \
        stored as parquet location                   \
        '{}/queue'"
driver.applySQL(CREATE_QUEUE_TABLE.format(repo_dir))

df = driver.applySQL('desc queue')
df.show()

driver.applySQL('drop table benchmark')
CREATE_BENCH_TABLE = "create external table          \
        benchmark(run_id int,                        \
        queue_id  int,                               \
        num_cores int,                               \
        compute_flops double,                        \
        memory_bw double,                            \
        network_bw double,                           \
        io_bw double,                                \
        acc_compute_flops double,                    \
        acc_memory_bw double,                        \
        PCIe_bw double)                              \
        stored as parquet location                   \
        '{}/benchmark'"
driver.applySQL(CREATE_BENCH_TABLE.format(repo_dir))

df = driver.applySQL('desc benchmark')
df.show()

driver.applySQL('drop table model')
CREATE_MODEL_TABLE = "create external table          \
        model(model_id int,                          \
        queue_id  int,                               \
        compute_flops string,                     \
        memory_bw string,                            \
        network_bw string,                           \
        io_bw string,                                \
        acc_compute_flops string,                    \
        acc_memory_bw string,                        \
        PCIe_bw string)                              \
        stored as parquet location                   \
        '{}/model'"
driver.applySQL(CREATE_MODEL_TABLE.format(repo_dir))

df = driver.applySQL('desc model')
df.show()

driver.applySQL('drop table appmodel')
CREATE_APPMODEL_TABLE = "create external table       \
        appmodel(appmodel_id int,                       \
        queue_id  int,                               \
        app_id  int,                                 \
        compute_flops double,                        \
        memory_bw double,                            \
        network_bw double,                           \
        io_bw double,                                \
        acc_compute_flops double,                    \
        acc_memory_bw double,                        \
        PCIe_bw double,                              \
        acc_share double)                            \
        stored as parquet location                   \
        '{}/appmodel'"
driver.applySQL(CREATE_APPMODEL_TABLE.format(repo_dir))

df = driver.applySQL('desc appmodel')
df.show()

driver.applySQL('drop table application')
CREATE_APP_TABLE = "create external table            \
        application(app_id int,                      \
        name  string,                                \
        app_type string,                             \
        description string,                          \
        src string)                                  \
        stored as parquet location                   \
        '{}/application'"
driver.applySQL(CREATE_APP_TABLE.format(repo_dir))

df = driver.applySQL('desc application')
df.show()

driver.applySQL('drop table audit_log')
CREATE_AUDIT_TABLE = "create external table         \
        audit_log(file_line  bigint,               \
        start_time timestamp,                       \
        end_time timestamp,                         \
        run_time_sec bigint,                        \
        queue_id  int,                              \
        app_id  int,                                \
        aprun_id bigint,                            \
        job_id string,                              \
        num_nodes int,                              \
        run_stat int,                               \
        command string,                             \
        command_uniq string)                        \
        stored as parquet location                  \
        '{}/audit_log'"
driver.applySQL(CREATE_AUDIT_TABLE.format(repo_dir))

df = driver.applySQL('desc audit_log')
df.show()

driver.applySQL('drop table optimisation')
CREATE_OPT_TABLE = "create external table            \
        optimisation(opt_id int,                      \
        opt_dsl_code  string,                                \
        app_name  string,                                \
        target string,                                  \
        optimisation string)                                  \
        stored as parquet location                   \
        '{}/optimisation'"
driver.applySQL(CREATE_OPT_TABLE.format(repo_dir))

df = driver.applySQL('desc optimisation')
df.show()

driver.applySQL('drop table mapper')
CREATE_MAPPER_TABLE = "create external table            \
        mapper(map_id int,                      \
        opt_dsl_code  string,                                \
        container_file string,                             \
        image_type string,                                  \
        image_hub string,                                  \
        src string)                                        \
        stored as parquet location                   \
        '{}/mapper'"
driver.applySQL(CREATE_MAPPER_TABLE.format(repo_dir))

df = driver.applySQL('desc mapper')
df.show()

df = driver.applySQL("SELECT * FROM mapper")
df.coalesce(1).write.option("header", "true").format("com.databricks.spark.csv").save("/Users/ksivalinga/Documents/CERL/sodalite/test-iac-model/mapper.csv")
df = driver.applySQL("SELECT * FROM optimisation")
df.coalesce(1).write.option("header", "true").format("com.databricks.spark.csv").save("/Users/ksivalinga/Documents/CERL/sodalite/test-iac-model/optimisation.csv")

driver.applySQL('select * from optimisation').show()

driver.applySQL('alter table mapper concatenate')

from mapper import mapper
m = mapper(driver)
print('Test mapper main')
# m.addContainer('TF_PIP_XLA','AI/containers/tensorflow/tensorflow_pip_xla','Docker')
m.add_optimisation('TF_PIP_XLA','tensorflow','cpu_type:x86,acc_type:nvidia','xla:true,version:1.1')
# print(m.getContainer('TF_PIP_XLA'))

m.add_optimisation(opt_dsl_code='TF_PIP_XLA',app_name='tensorflow',target='cpu_type:x86,acc_type:nvidia',optimisation='xla:true,version:1.1',version='2.1')

df = driver.applySQL("select opt_dsl_code from optimisation where app_name = 'tensorflow'\
and target like '%cpu_type:x86%' and target like '%acc_type:nvidia%' and \
optimisation like '%version:1.1%' and optimisation like '%xla:true%'")
df.show()

opt_dsl_code = 'TF_PIP_XLA'
stmt = "select container_file, image_hub from mapper \
                                  where opt_dsl_code='{}' order by map_id desc limit 1"
print(stmt.format(opt_dsl_code))
df = driver.applySQL(stmt.format(opt_dsl_code))
container_file = df.select('container_file').collect()[0][0]
image_hub = df.select('image_hub').collect()[0][0]
str(image_hub) + str('/')+str(container_file)

# +
from pyspark.sql import SparkSession

spark = SparkSession \
    .builder \
    .appName("Python Spark SQL basic example") \
    .config("spark.some.config.option", "some-value") \
    .getOrCreate()

df=spark.read.format("csv").option("header","true").option("inferSchema","true").load('/Users/ksivalinga/Documents/sodalite/iac-model-repo/db/data/mapper.csv')
df.write.mode("overwrite").saveAsTable("mapper")
# -

df = driver.applySQL("insert overwrite mapper select * from csv.`/Users/ksivalinga/Documents/sodalite/iac-model-repo/db/data/mapper.csv`")

driver.applySQL("select * from mapper").show()

# +
from pyspark.sql import SparkSession

spark = SparkSession \
    .builder \
    .appName("Python Spark SQL basic example") \
    .config("spark.some.config.option", "some-value") \
    .getOrCreate()

df=spark.read.format("csv").option("header","true").option("inferSchema","false").option("sep",",").load('/Users/ksivalinga/Documents/sodalite/iac-model-repo/db/data/optimisation.csv')
df.write.mode("overwrite").saveAsTable("optimisation")
# -

df = driver.applySQL("insert overwrite optimisation select * from csv.`/Users/ksivalinga/Documents/sodalite/iac-model-repo/db/data/optimisation.csv`")

driver.applySQL("select * from optimisation").show(50)

driver.applySQL("delete from optimisation where opt_dsl_code='opt_dsl_code' ")

# +
import os
from pyspark.sql import SQLContext
from pyspark import SparkContext
os.environ['PYSPARK_SUBMIT_ARGS'] = '--jars /Users/ksivalinga/Documents/build/mysql-connector-java-5.1.45/mysql-connector-java-5.1.45-bin.jar  pyspark-shell'


sc = SparkContext(appName="TestPySparkJDBC")
sqlContext = SQLContext(sc)

#Provide your Spark-master node below
hostname = "localhost" 
dbname = "mysql"
jdbcPort = 3306
username = "root"
password = "<PASSWORD>"
jdbc_url = "jdbc:mysql://{0}:{1}/{2}?user={3}&password={4}".format(hostname,jdbcPort, dbname,username,password)


query = "(select * from user) t1_alias"
df1 = sqlContext.read.format('jdbc').options(driver = 'com.mysql.jdbc.Driver',url=jdbc_url, dbtable=query ).load()
df1.show()
# -



StarcoderdataJupyterScriptsDedupFiltered