Zepl
Search…
MySQL

Create an MySQL Datasource

  1. 1.
    Navigate to: Resources > Data Sources
  2. 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
1
%python
2
import pandas as pd
3
# Connect to MySQL using attached Data Source object called zepl_docs_mysql
4
conn = z.getDatasource("zepl_docs_mysql")
5
​
6
# Create cursor object
7
cursor = conn.cursor()
8
cursor.execute("USE employees")
9
​
10
# Query Table customers, fetch results, and return results to a list object (res)
11
cursor.execute('SELECT * FROM customers')
12
res = cursor.fetchall()
13
​
14
# Create Pandas DataFrame from query results
15
df = pd.DataFrame(data=res, columns = cursor.column_names)
16
​
17
# Display results
18
z.show(df)
Copied!
Use z.getDatasource() to return MySQL connection object: mysql.connector.connect​
1
%spark
2
var url = "jdbc:mysql://<host>:<port>/<database>"
3
var table_name = "<table name>"
4
var driver = "com.mysql.jdbc.Driver" // Do not change
5
var user = "<username>"
6
var pass = "<password>"
7
​
8
// Create JDBC connection to MySQL using spark.read operations
9
val df = spark.read
10
.format("jdbc")
11
.option("driver", driver)
12
.option("url", url)
13
.option("dbtable", table_name)
14
.option("user", user)
15
.option("password", pass)
16
.load()
17
​
18
// Display DataFrame
19
z.show(df)
Copied!

Option 1: Use Zepl's Data Source

Please follow guidance from the Python tab

Option 2: Using SparkSQL

1
%pyspark
2
​
3
url = "jdbc:mysql://<hostname>:<port>/<database_name>"
4
table_name = "<table_name>"
5
driver = "com.mysql.jdbc.Driver" # Do not change
6
user = "<username>"
7
password = "<password>"
8
​
9
# Create JDBC connection to MySQL using spark.read operations
10
df = spark.read\
11
.format("jdbc")\
12
.option("driver", driver)\
13
.option("url", url)\
14
.option("dbtable", table_name)\
15
.option("user", user)\
16
.option("password", password)\
17
.load()
18
​
19
# Display DataFrame
20
z.show(df)
Copied!
1
%r
2
# TODO:
Copied!
1
%datasource.<data source name here>
2
# Specify db to query from
3
USE employees;
4
​
5
# Read data through 'select' statement
6
SELECT * FROM customers;
Copied!

Write

Python
Scala
PySpark
R
SQL
1
%python
2
import pandas as pd
3
​
4
# Connect to MySQL using attached Data Source. Create connection object
5
conn = z.getDatasource("zepl_docs_mysql")
6
# Create cursor object
7
cursor = conn.cursor()
8
​
9
# Create Database
10
cursor.execute("CREATE DATABASE if not exists employees DEFAULT CHARACTER SET 'utf8'")
11
​
12
# Select Database - Not required if added to the Data Source connection
13
cursor. execute("USE employees")
14
​
15
# Create Table
16
cursor.execute("CREATE TABLE if not exists customers (name VARCHAR(255), address VARCHAR(255))")
17
​
18
# Create data to Insert
19
data=(['Sean Connery', '6925 Hollywood Blvd, Hollywood, CA 90028'], ['Dolly Parton', '6712 Hollywood Boulevard, Hollywood, CA 90028'])
20
df = pd.DataFrame(data, columns=("name", "address"))
21
​
22
# Insert Data
23
sql = "INSERT INTO customers(name, address) values(%s, %s)"
24
cursor.execute(sql, data[0])
25
cursor.execute(sql, data[1])
26
​
27
# Commit insert statments
28
conn.commit()
29
​
30
# Close connection
31
cursor.close()
Copied!
1
%spark
2
import java.util.Properties
3
​
4
// Create Properties object for write function
5
val properties = new Properties();
6
​
7
// Create inputs
8
var url = "jdbc:mysql://<host>:<port>/<database>"
9
var table_name = "<table name>"
10
var driver = "com.mysql.jdbc.Driver" // Do not change
11
var user = "<username>"
12
var pass = "<password>"
13
​
14
// Set Properties
15
properties.setProperty("driver", driver);
16
properties.setProperty("user", user);
17
properties.setProperty("password", pass);
18
​
19
// Write df to MySQL table
20
df.write.mode("append").jdbc(url, table_name, properties)
Copied!

Option 1: Using Zepl's DataSource

Please follow guidance from the Python tab

Option 2: Using SparkSQL

1
%pyspark
2
url = "jdbc:mysql://<hostname>:<port>/<database_name>"
3
table_name = "<table_name>"
4
driver = "com.mysql.jdbc.Driver" # Do not change
5
user = "<username>"
6
password = "<password>"
7
​
8
df.write.format('jdbc').options(
9
url=url,
10
driver=driver,
11
dbtable=table_name,
12
user=user,
13
password=password)\
14
.mode('append')\
15
.save()
Copied!
1
%r
2
# TODO:
Copied!
1
%datasource.zepl_docs_mysql
2
# Specify db to query from
3
USE employees;
4
​
5
# Write data through 'insert' statement
6
INSERT INTO customers(name, address) values("Chris Rock", "7021 Hollywood Boulevard, Hollywood, CA 90028");
Copied!

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

​

Last modified 9mo ago