spa_rk_db

Question 1

topandas

Answer 1

spark.toPandas()

Question 2

from pandas

Answer 2

spark.createDataFrame(pandasdf, schema)

Question 3

read and write

Answer 3

spark. read()
spark. write()
spark. read.jdbl(url=database jdbc url path, table=”tablename”)
spark. write.mode(“overwrite/append”).jdbl(url=database jdbc url path, table=”tablename”)

#csv write
#save without changing the schema
??

Question 4

show

Answer 4

spark.show(no of rows to show as integer)

Question 5

filter based on condition

Answer 5

df. filter(“experiment_id = 1”).show()
df. filter((df.col1 == condition) & (df.col2 == condition))
df. filter((df.col1 == condition) & (df.col2 == condition)).select(colname)

Question 6

replace values on condition

Answer 6

df.replace(old,new)

Question 7

sparkdf select

Answer 7

sparkdf.select(“col name”)

Question 8

select row

Answer 8

df = spark.createDataFrame([[1,2], [3,4]], [‘a’, ‘b’])

n=1
df.select(df.columns[n]).show()
\+---+                                                                           
|  b|
\+---+
|  2|
|  4|
\+---+

df.drop(df.columns[n]).show()
\+---+
|  a|
\+---+
|  1|
|  3|
\+---+

df.select(df.columns[:n] + df.columns[n+1:]).show()

\+---+
|  a|
\+---+
|  1|
|  3|
\+---+

Question 9

import pyspark types to construct schema

Answer 9

from pypark.sql.types import *

Question 10

run sql query

Answer 10

spark.sql(“select * from table –limt 100”)

Question 11

connect to an external database using pyodbc

Answer 11

import pyodbc

Driver=’{ODBC Driver 17 for SQL Server}’
Server= sql_server
Database=sql_db

cnxn = pyodbc.connect(‘DRIVER=’+Driver+’;SERVER=’+Server.split(“:”)[0]+’;DATABASE=’+Database+’;UID=’+sql_user+’;PWD=’+sql_pass)

cnxn = pyodbc.connect(‘DRIVER=’+Driver+’;SERVER=’+Server.split(“:”)[0]+’;DATABASE=’+Database+’;UID=’+sql_user+’;PWD=’+sql_pass)

cursor = cnxn.cursor()

cursor. execute(“update Experiment set experimentStatus = ‘Completed’ where experimentId = {}”.format(experiment_id))
cnxn. commit()

Question 12

get secrets in databricks

Answer 12

dbutils.secrets.get(‘nlpexplorer-secret-scope ‘,’sql-username ‘)

first create the key:value pair in key vault

Question 13

create secrets in databricks

Answer 13

https://docs.microsoft.com/en-us/azure/databricks/security/secrets/secret-scopes

Question 14

databricks jdbc url

Answer 14

Obtain from database

Question 15

curl comments for installing sql driver

Answer 15

%sh
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get -q -y install msodbcsql17

Question 16

change datatype in pyspark

Answer 16

df2 = df.withColumn(“age”,col(“age”).cast(StringType())

https://sparkbyexamples.com/pyspark/pyspark-cast-column-type/

Question 17

change datatype in pyspark.

With column
with select
with select exp
with spark sql

Answer 17

// Convert String to Integer Type

df. withColumn(“salary”,col(“salary”).cast(IntegerType))
df. withColumn(“salary”,col(“salary”).cast(“int”))
df. withColumn(“salary”,col(“salary”).cast(“integer”))

// Using select
df.select(col("salary").cast("int").as("salary"))

//Using selectExpr()

df. selectExpr(“cast(salary as int) salary”,”isGraduated”)
df. selectExpr(“INT(salary)”,”isGraduated”)

//Using with spark.sql()

spark. sql(“SELECT INT(salary),BOOLEAN(isGraduated),gender from CastExample”)
spark. sql(“SELECT cast(salary as int) salary, BOOLEAN(isGraduated),gender from CastExample”)

Question 18

databricks dbutils to get parameters

Answer 18

dbutils.widgets.text(“ExperimentId”,”DefaultName”)

experiment_id = dbutils.widgets.get(“ExperimentId”)

Question 19

call db notebook from another notebook

Answer 19

dbutils.notebook.run(“notebook name”,timeout, {})

Question 20

send results out of databricks after run

Answer 20

dbutils.notebook.exit(“value”)

import json
dbutils.notebook.exit(json({key:value}))

Question 21

add arbitrary column to spark

Answer 21

from pyspark.sql.functions import lit

df_with_x4 = df.withColumn(“x4”, lit(0))
df_with_x4.show()

Question 22

get no of rows

Answer 22

sparkdf.count()

Question 23

get column

Answer 23

sparkdf.column

Question 24

turn databricks notebook cell to run sql commands

Answer 24

%sql

sql query

Question 25

#http requests
Using job id

Answer 25

1. create a job in databricks using jobs
2. copy job id
3. in postman create post request
4. add authorisation token (get it from user settings)
5. . add API request
   https: //adb-3184120056443208.8.azuredatabricks.net/api/2.0/jobs/run-now
6. set body to json
7. add json strings

{
"job_id":5670,
"notebook_params":{
    "lob": "4",
    "dataSource": "CAP",
    "customerGeos": "107,1",
    "textFields": "9",
    "channel": "13",
    "cssProducts": "16",
    "issueCodes": "37056"},
"notebook_task": {"notebook_path": "/Nlp_explorer/Casecount"
            }}

Question 26

#http requests
Using run id

Answer 26

https://adb-3184120056443208.8.azuredatabricks.net/api/2.0/jobs/runs/submit

{
"existing_cluster_id": "0510-115652-stop848",
"notebook_params":{
    "lob": "4",
    "dataSource": "CAP",
    "customerGeos": "107,1",
    "textFields": "9",
    "channel": "13",
    "cssProducts": "16",
    "issueCodes": "37056"},
"notebook_task": {"notebook_path": "/Nlp_explorer/Casecount"
            }}

Question 27

get for run id

Answer 27

https://adb-3184120056443208.8.azuredatabricks.net/api/2.0/get

{
“run_id”: 5670
}

Question 28

create empty dataframe

Answer 28

spark.createDataFrame([],[“col1”, “col2”])

Question 29

how to check if df is empty

Answer 29

df.rdd.isEmpty()

Question 30

read csv with header

Answer 30

medium article has more on read and write with jsons also

spark.read.format(“csv).option(“header”.”true”).load(filepath)

Question 31

overwrite table without changing schema

Answer 31

trans something………… forgot

Question 32

storing the password or key in Azure Key Vault

Answer 32

storing the password or key in Azure Key Vault as a secret instead in a notebook (SQL query).

dbutils.secrets.get(scope = “bitools_secrets”, key = “blobkey”)