MySQL

Create an MySQL Datasource
1.Navigate to: Resources > Data Sources
2.Select the MySQL Icon:
Data Source Inputs
Name (required)
Host (required): 
Enter the hostname of your mysql database. 
Example (using MySQL in AWS RDS): zepl-db-test.sddks8d7d89s.us-west-1.rds.amazonaws.com
Port (required): 3306 is the default port for MySQL
Database (optional): 
If this is filled in, Zepl will append the database name to your connection string. 
Example:zepl-db-test.sddks8d7d89s.us-west-1.rds.amazonaws.com/<database name>
Credential Type: See Data Source Security​
Username (required - can be entered later): Username used for connecting to MySQL
Password (required - can be entered later): Password used for connecting to MySQL
Use MySQL
Read
Python
Scala
PySpark
R
SQL
%python
import pandas as pd
# Connect to MySQL using attached Data Source object called zepl_docs_mysql
conn = z.getDatasource("zepl_docs_mysql")
​
# Create cursor object
cursor = conn.cursor()
cursor.execute("USE employees")
​
# Query Table customers, fetch results, and return results to a list object (res)
cursor.execute('SELECT * FROM customers')
res = cursor.fetchall()
​
# Create Pandas DataFrame from query results
df = pd.DataFrame(data=res, columns = cursor.column_names)
​
# Display results
z.show(df)
Use z.getDatasource()  to return MySQL connection object: mysql.connector.connect​
%spark
var url = "jdbc:mysql://<host>:<port>/<database>"
var table_name = "<table name>"
var driver = "com.mysql.jdbc.Driver" // Do not change  
var user = "<username>"
var pass = "<password>"
​
// Create JDBC connection to MySQL using spark.read operations
val df = spark.read
  .format("jdbc")
  .option("driver", driver)
  .option("url", url)
  .option("dbtable", table_name)
  .option("user", user)
  .option("password", pass)
  .load()
​
// Display DataFrame
z.show(df)
Option 1: Use Zepl's Data Source
Please follow guidance from the Python tab 
Option 2: Using SparkSQL
%pyspark
​
url = "jdbc:mysql://<hostname>:<port>/<database_name>"
table_name = "<table_name>"
driver = "com.mysql.jdbc.Driver" # Do not change  
user = "<username>"
password = "<password>"
​
# Create JDBC connection to MySQL using spark.read operations
df = spark.read\
  .format("jdbc")\
  .option("driver", driver)\
  .option("url", url)\
  .option("dbtable", table_name)\
  .option("user", user)\
  .option("password", password)\
  .load()
​
# Display DataFrame
z.show(df)
%r
# TODO:
%datasource.<data source name here> 
# Specify db to query from
USE employees;
​
# Read data through 'select' statement
SELECT * FROM customers;
Write
Python
Scala
PySpark
R
SQL
%python
import pandas as pd
​
# Connect to MySQL using attached Data Source. Create connection object
conn = z.getDatasource("zepl_docs_mysql")
# Create cursor object
cursor = conn.cursor()
​
# Create Database
cursor.execute("CREATE DATABASE if not exists employees DEFAULT CHARACTER SET 'utf8'")
​
# Select Database - Not required if added to the Data Source connection
cursor. execute("USE employees")
​
# Create Table
cursor.execute("CREATE TABLE if not exists customers (name VARCHAR(255), address VARCHAR(255))")
​
# Create data to Insert
data=(['Sean Connery', '6925 Hollywood Blvd, Hollywood, CA 90028'], ['Dolly Parton', '6712 Hollywood Boulevard, Hollywood, CA 90028'])
df = pd.DataFrame(data, columns=("name", "address"))
​
# Insert Data
sql = "INSERT INTO customers(name, address) values(%s, %s)"
cursor.execute(sql, data[0])
cursor.execute(sql, data[1])
​
# Commit insert statments
conn.commit()
​
# Close connection
cursor.close()
%spark
import java.util.Properties
​
// Create Properties object for write function
val properties = new Properties();
​
// Create inputs
var url = "jdbc:mysql://<host>:<port>/<database>"
var table_name = "<table name>"
var driver = "com.mysql.jdbc.Driver" // Do not change  
var user = "<username>"
var pass = "<password>"
​
// Set Properties
properties.setProperty("driver", driver);
properties.setProperty("user", user);
properties.setProperty("password", pass);
​
// Write df to MySQL table
df.write.mode("append").jdbc(url, table_name, properties)
Option 1: Using Zepl's DataSource
Please follow guidance from the Python tab 
Option 2: Using SparkSQL
%pyspark
url = "jdbc:mysql://<hostname>:<port>/<database_name>"
table_name = "<table_name>"
driver = "com.mysql.jdbc.Driver" # Do not change  
user = "<username>"
password = "<password>"
​
df.write.format('jdbc').options(
      url=url,
      driver=driver,
      dbtable=table_name,
      user=user,
      password=password)\
      .mode('append')\
      .save()
%r
# TODO:
%datasource.zepl_docs_mysql
# Specify db to query from
USE employees;
​
# Write data through 'insert' statement
INSERT INTO customers(name, address) values("Chris Rock", "7021 Hollywood Boulevard, Hollywood, CA 90028");
​Open all examples in Zepl​
Configure Authentication in MySQL
In your MySQL database be sure that these items are configured properly:
MySQL should be accessible to Zepl's whitelisted IP addresses
The user associated with your user name and password have access to the desired databases and tables
User should have read and write permissions on the desired databases and tables
​