{ "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "\n", " You are using PySparkling of version 2.4.10, but your PySpark is of\n", " version 2.3.1. Please make sure Spark and PySparkling versions are compatible. \n", "INFO:optimus:Operative System:Windows\n", "INFO:optimus:Just check that Spark and all necessary environments vars are present...\n", "INFO:optimus:-----\n", "INFO:optimus:SPARK_HOME=C:\\opt\\spark\\spark-2.3.1-bin-hadoop2.7\n", "INFO:optimus:HADOOP_HOME=C:\\opt\\hadoop-2.7.7\n", "INFO:optimus:PYSPARK_PYTHON=C:\\Users\\argenisleon\\Anaconda3\\python.exe\n", "INFO:optimus:PYSPARK_DRIVER_PYTHON=jupyter\n", "INFO:optimus:PYSPARK_SUBMIT_ARGS=--jars \"file:///C:/Users/argenisleon/Documents/Optimus/optimus/jars/spark-redis-2.4.1-SNAPSHOT-jar-with-dependencies.jar,file:///C:/Users/argenisleon/Documents/Optimus/optimus/jars/RedshiftJDBC42-1.2.16.1027.jar,file:///C:/Users/argenisleon/Documents/Optimus/optimus/jars/mysql-connector-java-8.0.16.jar,file:///C:/Users/argenisleon/Documents/Optimus/optimus/jars/ojdbc8.jar,file:///C:/Users/argenisleon/Documents/Optimus/optimus/jars/postgresql-42.2.5.jar,file:///C:/Users/argenisleon/Documents/Optimus/optimus/jars/presto-jdbc-0.224.jar,file:///C:/Users/argenisleon/Documents/Optimus/optimus/jars/spark-cassandra-connector_2.11-2.4.1.jar,file:///C:/Users/argenisleon/Documents/Optimus/optimus/jars/sqlite-jdbc-3.27.2.1.jar,file:///C:/Users/argenisleon/Documents/Optimus/optimus/jars/mssql-jdbc-7.4.1.jre8.jar\" --driver-class-path \"C:/Users/argenisleon/Documents/Optimus/optimus/jars/spark-redis-2.4.1-SNAPSHOT-jar-with-dependencies.jar;C:/Users/argenisleon/Documents/Optimus/optimus/jars/RedshiftJDBC42-1.2.16.1027.jar;C:/Users/argenisleon/Documents/Optimus/optimus/jars/mysql-connector-java-8.0.16.jar;C:/Users/argenisleon/Documents/Optimus/optimus/jars/ojdbc8.jar;C:/Users/argenisleon/Documents/Optimus/optimus/jars/postgresql-42.2.5.jar;C:/Users/argenisleon/Documents/Optimus/optimus/jars/presto-jdbc-0.224.jar;C:/Users/argenisleon/Documents/Optimus/optimus/jars/spark-cassandra-connector_2.11-2.4.1.jar;C:/Users/argenisleon/Documents/Optimus/optimus/jars/sqlite-jdbc-3.27.2.1.jar;C:/Users/argenisleon/Documents/Optimus/optimus/jars/mssql-jdbc-7.4.1.jre8.jar\" --conf \"spark.sql.catalogImplementation=hive\" pyspark-shell\n", "INFO:optimus:JAVA_HOME=C:\\java\n", "INFO:optimus:Pyarrow Installed\n", "INFO:optimus:-----\n", "INFO:optimus:Starting or getting SparkSession and SparkContext...\n", "INFO:optimus:Spark Version:2.3.1\n", "INFO:optimus:\n", " ____ __ _ \n", " / __ \\____ / /_(_)___ ___ __ _______\n", " / / / / __ \\/ __/ / __ `__ \\/ / / / ___/\n", " / /_/ / /_/ / /_/ / / / / / / /_/ (__ ) \n", " \\____/ .___/\\__/_/_/ /_/ /_/\\__,_/____/ \n", " /_/ \n", " \n", "INFO:optimus:Transform and Roll out...\n", "INFO:optimus:Optimus successfully imported. Have fun :).\n", "INFO:optimus:Config.ini not found\n" ] } ], "source": [ "%load_ext autoreload\n", "%autoreload 2\n", "import sys\n", "sys.path.append(\"..\")\n", "from optimus import Optimus\n", "\n", "# Create optimus\n", "op = Optimus(master=\"local\", app_name= \"optimus\", verbose = True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Mysql" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:jdbc:mysql://165.227.196.70:3306/optimus?currentSchema=public\n" ] } ], "source": [ "# Put your db credentials here\n", "db = op.connect(\n", " driver=\"mysql\",\n", " host=\"165.227.196.70\", \n", " database= \"optimus\", \n", " user= \"test\", \n", " password = \"test\")" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:(SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'optimus' LIMIT 10 ) AS t\n", "INFO:optimus:jdbc:mysql://165.227.196.70:3306/optimus?currentSchema=public\n" ] }, { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "
Viewing 1 of 1 rows / 2 columns
\n", "
1 partition(s)
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
TABLE_NAME
\n", "
1 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
TABLE_ROWS
\n", "
2 (decimal(20,0))
\n", "
\n", " \n", " nullable\n", " \n", "
\n", "
\n", "
\n", " \n", " test_data\n", " \n", "
\n", " 		\n", "
\n", " \n", " 100\n", " \n", "
\n", "
\n", "\n", "\n", "
Viewing 1 of 1 rows / 2 columns
\n", "
1 partition(s)
\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "db.tables()" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "# db.execute(\"SHOW KEYS FROM test_data WHERE key_name = 'PRIMARY'\")" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:(SELECT * FROM test_data LIMIT 10 ) AS t\n", "INFO:optimus:jdbc:mysql://165.227.196.70:3306/optimus?currentSchema=public\n" ] }, { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "
Viewing 10 of 10 rows / 6 columns
\n", "
1 partition(s)
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
id
\n", "
1 (int)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
first_name
\n", "
2 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
last_name
\n", "
3 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
email
\n", "
4 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
gender
\n", "
5 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
ip_address
\n", "
6 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", "
\n", "
\n", " \n", " 1\n", " \n", "
\n", " 		\n", "
\n", " \n", " Ikey\n", " \n", "
\n", " 		\n", "
\n", " \n", " Crudginton\n", " \n", "
\n", " 		\n", "
\n", " \n", " icrudginton0@freewebs.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 72.210.21.255\n", " \n", "
\n", "
\n", "
\n", " \n", " 2\n", " \n", "
\n", " 		\n", "
\n", " \n", " Erwin\n", " \n", "
\n", " 		\n", "
\n", " \n", " Edden\n", " \n", "
\n", " 		\n", "
\n", " \n", " eedden1@nytimes.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 16.205.155.142\n", " \n", "
\n", "
\n", "
\n", " \n", " 3\n", " \n", "
\n", " 		\n", "
\n", " \n", " Rudyard\n", " \n", "
\n", " 		\n", "
\n", " \n", " Dullaghan\n", " \n", "
\n", " 		\n", "
\n", " \n", " rdullaghan2@techcrunch.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 84.170.67.167\n", " \n", "
\n", "
\n", "
\n", " \n", " 4\n", " \n", "
\n", " 		\n", "
\n", " \n", " Eugen\n", " \n", "
\n", " 		\n", "
\n", " \n", " Staining\n", " \n", "
\n", " 		\n", "
\n", " \n", " estaining3@merriam-webster.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 211.36.45.228\n", " \n", "
\n", "
\n", "
\n", " \n", " 5\n", " \n", "
\n", " 		\n", "
\n", " \n", " Carleton\n", " \n", "
\n", " 		\n", "
\n", " \n", " Hammond\n", " \n", "
\n", " 		\n", "
\n", " \n", " chammond4@example.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 177.7.250.134\n", " \n", "
\n", "
\n", "
\n", " \n", " 6\n", " \n", "
\n", " 		\n", "
\n", " \n", " Ermengarde\n", " \n", "
\n", " 		\n", "
\n", " \n", " Knightly\n", " \n", "
\n", " 		\n", "
\n", " \n", " eknightly5@google.co.jp\n", " \n", "
\n", " 		\n", "
\n", " \n", " Female\n", " \n", "
\n", " 		\n", "
\n", " \n", " 231.176.117.190\n", " \n", "
\n", "
\n", "
\n", " \n", " 7\n", " \n", "
\n", " 		\n", "
\n", " \n", " Myles\n", " \n", "
\n", " 		\n", "
\n", " \n", " Rattray\n", " \n", "
\n", " 		\n", "
\n", " \n", " mrattray6@about.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 4.193.247.67\n", " \n", "
\n", "
\n", "
\n", " \n", " 8\n", " \n", "
\n", " 		\n", "
\n", " \n", " Banky\n", " \n", "
\n", " 		\n", "
\n", " \n", " Shires\n", " \n", "
\n", " 		\n", "
\n", " \n", " bshires7@so-net.ne.jp\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 16.18.210.158\n", " \n", "
\n", "
\n", "
\n", " \n", " 9\n", " \n", "
\n", " 		\n", "
\n", " \n", " Chastity\n", " \n", "
\n", " 		\n", "
\n", " \n", " Birtwell\n", " \n", "
\n", " 		\n", "
\n", " \n", " cbirtwell8@seesaa.net\n", " \n", "
\n", " 		\n", "
\n", " \n", " Female\n", " \n", "
\n", " 		\n", "
\n", " \n", " 167.15.222.219\n", " \n", "
\n", "
\n", "
\n", " \n", " 10\n", " \n", "
\n", " 		\n", "
\n", " \n", " Harv\n", " \n", "
\n", " 		\n", "
\n", " \n", " Fotherby\n", " \n", "
\n", " 		\n", "
\n", " \n", " hfotherby9@godaddy.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 143.117.248.106\n", " \n", "
\n", "
\n", "\n", "\n", "
Viewing 10 of 10 rows / 6 columns
\n", "
1 partition(s)
\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "db.execute(\"SELECT * FROM test_data\").ext.display()" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:(SELECT * FROM test_data LIMIT 10 ) AS t\n", "INFO:optimus:jdbc:mysql://165.227.196.70:3306/optimus?currentSchema=public\n", "INFO:optimus:(SELECT min(id) AS min, max(id) AS max FROM test_data LIMIT 10 ) AS t\n", "INFO:optimus:jdbc:mysql://165.227.196.70:3306/optimus?currentSchema=public\n" ] }, { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "
Viewing 10 of 10 rows / 6 columns
\n", "
10 partition(s)
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
id
\n", "
1 (int)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
first_name
\n", "
2 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
last_name
\n", "
3 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
email
\n", "
4 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
gender
\n", "
5 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
ip_address
\n", "
6 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", "
\n", "
\n", " \n", " 1\n", " \n", "
\n", " 		\n", "
\n", " \n", " Ikey\n", " \n", "
\n", " 		\n", "
\n", " \n", " Crudginton\n", " \n", "
\n", " 		\n", "
\n", " \n", " icrudginton0@freewebs.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 72.210.21.255\n", " \n", "
\n", "
\n", "
\n", " \n", " 2\n", " \n", "
\n", " 		\n", "
\n", " \n", " Erwin\n", " \n", "
\n", " 		\n", "
\n", " \n", " Edden\n", " \n", "
\n", " 		\n", "
\n", " \n", " eedden1@nytimes.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 16.205.155.142\n", " \n", "
\n", "
\n", "
\n", " \n", " 3\n", " \n", "
\n", " 		\n", "
\n", " \n", " Rudyard\n", " \n", "
\n", " 		\n", "
\n", " \n", " Dullaghan\n", " \n", "
\n", " 		\n", "
\n", " \n", " rdullaghan2@techcrunch.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 84.170.67.167\n", " \n", "
\n", "
\n", "
\n", " \n", " 4\n", " \n", "
\n", " 		\n", "
\n", " \n", " Eugen\n", " \n", "
\n", " 		\n", "
\n", " \n", " Staining\n", " \n", "
\n", " 		\n", "
\n", " \n", " estaining3@merriam-webster.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 211.36.45.228\n", " \n", "
\n", "
\n", "
\n", " \n", " 5\n", " \n", "
\n", " 		\n", "
\n", " \n", " Carleton\n", " \n", "
\n", " 		\n", "
\n", " \n", " Hammond\n", " \n", "
\n", " 		\n", "
\n", " \n", " chammond4@example.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 177.7.250.134\n", " \n", "
\n", "
\n", "
\n", " \n", " 6\n", " \n", "
\n", " 		\n", "
\n", " \n", " Ermengarde\n", " \n", "
\n", " 		\n", "
\n", " \n", " Knightly\n", " \n", "
\n", " 		\n", "
\n", " \n", " eknightly5@google.co.jp\n", " \n", "
\n", " 		\n", "
\n", " \n", " Female\n", " \n", "
\n", " 		\n", "
\n", " \n", " 231.176.117.190\n", " \n", "
\n", "
\n", "
\n", " \n", " 7\n", " \n", "
\n", " 		\n", "
\n", " \n", " Myles\n", " \n", "
\n", " 		\n", "
\n", " \n", " Rattray\n", " \n", "
\n", " 		\n", "
\n", " \n", " mrattray6@about.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 4.193.247.67\n", " \n", "
\n", "
\n", "
\n", " \n", " 8\n", " \n", "
\n", " 		\n", "
\n", " \n", " Banky\n", " \n", "
\n", " 		\n", "
\n", " \n", " Shires\n", " \n", "
\n", " 		\n", "
\n", " \n", " bshires7@so-net.ne.jp\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 16.18.210.158\n", " \n", "
\n", "
\n", "
\n", " \n", " 9\n", " \n", "
\n", " 		\n", "
\n", " \n", " Chastity\n", " \n", "
\n", " 		\n", "
\n", " \n", " Birtwell\n", " \n", "
\n", " 		\n", "
\n", " \n", " cbirtwell8@seesaa.net\n", " \n", "
\n", " 		\n", "
\n", " \n", " Female\n", " \n", "
\n", " 		\n", "
\n", " \n", " 167.15.222.219\n", " \n", "
\n", "
\n", "
\n", " \n", " 10\n", " \n", "
\n", " 		\n", "
\n", " \n", " Harv\n", " \n", "
\n", " 		\n", "
\n", " \n", " Fotherby\n", " \n", "
\n", " 		\n", "
\n", " \n", " hfotherby9@godaddy.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 143.117.248.106\n", " \n", "
\n", "
\n", "\n", "\n", "
Viewing 10 of 10 rows / 6 columns
\n", "
10 partition(s)
\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "db.execute(\"SELECT * FROM test_data\", partition_column =\"id\", table_name = \"test_data\").ext.display()" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:SELECT * FROM test_data\n", "INFO:optimus:(SELECT * FROM test_data LIMIT 10 ) AS t\n", "INFO:optimus:jdbc:mysql://165.227.196.70:3306/optimus?currentSchema=public\n", "INFO:optimus:(SELECT min(id) AS min, max(id) AS max FROM test_data LIMIT 10 ) AS t\n", "INFO:optimus:jdbc:mysql://165.227.196.70:3306/optimus?currentSchema=public\n" ] }, { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "
Viewing 10 of 10 rows / 6 columns
\n", "
10 partition(s)
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
id
\n", "
1 (int)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
first_name
\n", "
2 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
last_name
\n", "
3 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
email
\n", "
4 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
gender
\n", "
5 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
ip_address
\n", "
6 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", "
\n", "
\n", " \n", " 1\n", " \n", "
\n", " 		\n", "
\n", " \n", " Ikey\n", " \n", "
\n", " 		\n", "
\n", " \n", " Crudginton\n", " \n", "
\n", " 		\n", "
\n", " \n", " icrudginton0@freewebs.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 72.210.21.255\n", " \n", "
\n", "
\n", "
\n", " \n", " 2\n", " \n", "
\n", " 		\n", "
\n", " \n", " Erwin\n", " \n", "
\n", " 		\n", "
\n", " \n", " Edden\n", " \n", "
\n", " 		\n", "
\n", " \n", " eedden1@nytimes.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 16.205.155.142\n", " \n", "
\n", "
\n", "
\n", " \n", " 3\n", " \n", "
\n", " 		\n", "
\n", " \n", " Rudyard\n", " \n", "
\n", " 		\n", "
\n", " \n", " Dullaghan\n", " \n", "
\n", " 		\n", "
\n", " \n", " rdullaghan2@techcrunch.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 84.170.67.167\n", " \n", "
\n", "
\n", "
\n", " \n", " 4\n", " \n", "
\n", " 		\n", "
\n", " \n", " Eugen\n", " \n", "
\n", " 		\n", "
\n", " \n", " Staining\n", " \n", "
\n", " 		\n", "
\n", " \n", " estaining3@merriam-webster.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 211.36.45.228\n", " \n", "
\n", "
\n", "
\n", " \n", " 5\n", " \n", "
\n", " 		\n", "
\n", " \n", " Carleton\n", " \n", "
\n", " 		\n", "
\n", " \n", " Hammond\n", " \n", "
\n", " 		\n", "
\n", " \n", " chammond4@example.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 177.7.250.134\n", " \n", "
\n", "
\n", "
\n", " \n", " 6\n", " \n", "
\n", " 		\n", "
\n", " \n", " Ermengarde\n", " \n", "
\n", " 		\n", "
\n", " \n", " Knightly\n", " \n", "
\n", " 		\n", "
\n", " \n", " eknightly5@google.co.jp\n", " \n", "
\n", " 		\n", "
\n", " \n", " Female\n", " \n", "
\n", " 		\n", "
\n", " \n", " 231.176.117.190\n", " \n", "
\n", "
\n", "
\n", " \n", " 7\n", " \n", "
\n", " 		\n", "
\n", " \n", " Myles\n", " \n", "
\n", " 		\n", "
\n", " \n", " Rattray\n", " \n", "
\n", " 		\n", "
\n", " \n", " mrattray6@about.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 4.193.247.67\n", " \n", "
\n", "
\n", "
\n", " \n", " 8\n", " \n", "
\n", " 		\n", "
\n", " \n", " Banky\n", " \n", "
\n", " 		\n", "
\n", " \n", " Shires\n", " \n", "
\n", " 		\n", "
\n", " \n", " bshires7@so-net.ne.jp\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 16.18.210.158\n", " \n", "
\n", "
\n", "
\n", " \n", " 9\n", " \n", "
\n", " 		\n", "
\n", " \n", " Chastity\n", " \n", "
\n", " 		\n", "
\n", " \n", " Birtwell\n", " \n", "
\n", " 		\n", "
\n", " \n", " cbirtwell8@seesaa.net\n", " \n", "
\n", " 		\n", "
\n", " \n", " Female\n", " \n", "
\n", " 		\n", "
\n", " \n", " 167.15.222.219\n", " \n", "
\n", "
\n", "
\n", " \n", " 10\n", " \n", "
\n", " 		\n", "
\n", " \n", " Harv\n", " \n", "
\n", " 		\n", "
\n", " \n", " Fotherby\n", " \n", "
\n", " 		\n", "
\n", " \n", " hfotherby9@godaddy.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 143.117.248.106\n", " \n", "
\n", "
\n", "\n", "\n", "
Viewing 10 of 10 rows / 6 columns
\n", "
10 partition(s)
\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "db.table_to_df(\"test_data\", partition_column =\"id\").ext.display()" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:SELECT * FROM test_data\n", "INFO:optimus:(SELECT * FROM test_data LIMIT 10 ) AS t\n", "INFO:optimus:jdbc:mysql://165.227.196.70:3306/optimus?currentSchema=public\n" ] } ], "source": [ "df = db.table_to_df(\"test_data\", limit=None)" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:(SELECT TABLE_NAME AS table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'optimus' GROUP BY TABLE_NAME ) AS t\n", "INFO:optimus:jdbc:mysql://165.227.196.70:3306/optimus?currentSchema=public\n" ] }, { "data": { "text/plain": [ "['test_data']" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db.tables_names_to_json()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Postgres" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:jdbc:postgresql://165.227.196.70:5432/optimus?currentSchema=public\n" ] } ], "source": [ "# Put your db credentials here\n", "db = op.connect(\n", " driver=\"postgresql\",\n", " host=\"165.227.196.70\", \n", " database= \"optimus\", \n", " user= \"testuser\", \n", " password = \"test\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "db.tables()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "db.table_to_df(\"test_data\").table()" ] }, { "cell_type": "code", "execution_count": 69, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:(\n", " SELECT relname as table_name \n", " FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) \n", " WHERE nspname IN ('public') AND relkind='r' ORDER BY reltuples DESC) AS t\n", "INFO:optimus:jdbc:postgresql://165.227.196.70:5432/optimus?currentSchema=public\n" ] }, { "data": { "text/plain": [ "['test_data']" ] }, "execution_count": 69, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db.tables_names_to_json()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## MSSQL" ] }, { "cell_type": "code", "execution_count": 70, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:jdbc:sqlserver://165.227.196.70:1433;databaseName=optimus\n" ] } ], "source": [ "# Put your db credentials here\n", "db = op.connect(\n", " driver=\"sqlserver\",\n", " host=\"165.227.196.70\", \n", " database= \"optimus\", \n", " user= \"test\", \n", " password = \"test*0261\")" ] }, { "cell_type": "code", "execution_count": 71, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:(SELECT * FROM INFORMATION_SCHEMA.TABLES) AS t\n", "INFO:optimus:jdbc:sqlserver://165.227.196.70:1433;databaseName=optimus\n" ] }, { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "
Viewing 1 of 1 rows / 4 columns
\n", "
1 partition(s)
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
TABLE_CATALOG
\n", "
1 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
TABLE_SCHEMA
\n", "
2 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
TABLE_NAME
\n", "
3 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
TABLE_TYPE
\n", "
4 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", "
\n", "
\n", " \n", " optimus\n", " \n", "
\n", " 		\n", "
\n", " \n", " dbo\n", " \n", "
\n", " 		\n", "
\n", " \n", " test_data\n", " \n", "
\n", " 		\n", "
\n", " \n", " BASE⋅TABLE\n", " \n", "
\n", "
\n", "\n", "\n", "
Viewing 1 of 1 rows / 4 columns
\n", "
1 partition(s)
\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "db.tables()" ] }, { "cell_type": "code", "execution_count": 72, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:SELECT * FROM test_data\n", "INFO:optimus:(SELECT * FROM test_data) AS t\n", "INFO:optimus:jdbc:sqlserver://165.227.196.70:1433;databaseName=optimus\n" ] }, { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "
Viewing 10 of 10 rows / 6 columns
\n", "
1 partition(s)
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
id
\n", "
1 (int)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
first_name
\n", "
2 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
last_name
\n", "
3 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
email
\n", "
4 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
gender
\n", "
5 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
ip_address
\n", "
6 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", "
\n", "
\n", " \n", " 1\n", " \n", "
\n", " 		\n", "
\n", " \n", " Keenan\n", " \n", "
\n", " 		\n", "
\n", " \n", " McAirt\n", " \n", "
\n", " 		\n", "
\n", " \n", " kmcairt0@spotify.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 68.97.227.147\n", " \n", "
\n", "
\n", "
\n", " \n", " 2\n", " \n", "
\n", " 		\n", "
\n", " \n", " Fredelia\n", " \n", "
\n", " 		\n", "
\n", " \n", " Lemarie\n", " \n", "
\n", " 		\n", "
\n", " \n", " flemarie1@furl.net\n", " \n", "
\n", " 		\n", "
\n", " \n", " Female\n", " \n", "
\n", " 		\n", "
\n", " \n", " 16.145.123.46\n", " \n", "
\n", "
\n", "
\n", " \n", " 1\n", " \n", "
\n", " 		\n", "
\n", " \n", " Keenan\n", " \n", "
\n", " 		\n", "
\n", " \n", " McAirt\n", " \n", "
\n", " 		\n", "
\n", " \n", " kmcairt0@spotify.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 68.97.227.147\n", " \n", "
\n", "
\n", "
\n", " \n", " 2\n", " \n", "
\n", " 		\n", "
\n", " \n", " Fredelia\n", " \n", "
\n", " 		\n", "
\n", " \n", " Lemarie\n", " \n", "
\n", " 		\n", "
\n", " \n", " flemarie1@furl.net\n", " \n", "
\n", " 		\n", "
\n", " \n", " Female\n", " \n", "
\n", " 		\n", "
\n", " \n", " 16.145.123.46\n", " \n", "
\n", "
\n", "
\n", " \n", " 1\n", " \n", "
\n", " 		\n", "
\n", " \n", " Keenan\n", " \n", "
\n", " 		\n", "
\n", " \n", " McAirt\n", " \n", "
\n", " 		\n", "
\n", " \n", " kmcairt0@spotify.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 68.97.227.147\n", " \n", "
\n", "
\n", "
\n", " \n", " 2\n", " \n", "
\n", " 		\n", "
\n", " \n", " Fredelia\n", " \n", "
\n", " 		\n", "
\n", " \n", " Lemarie\n", " \n", "
\n", " 		\n", "
\n", " \n", " flemarie1@furl.net\n", " \n", "
\n", " 		\n", "
\n", " \n", " Female\n", " \n", "
\n", " 		\n", "
\n", " \n", " 16.145.123.46\n", " \n", "
\n", "
\n", "
\n", " \n", " 1\n", " \n", "
\n", " 		\n", "
\n", " \n", " Keenan\n", " \n", "
\n", " 		\n", "
\n", " \n", " McAirt\n", " \n", "
\n", " 		\n", "
\n", " \n", " kmcairt0@spotify.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 68.97.227.147\n", " \n", "
\n", "
\n", "
\n", " \n", " 2\n", " \n", "
\n", " 		\n", "
\n", " \n", " Fredelia\n", " \n", "
\n", " 		\n", "
\n", " \n", " Lemarie\n", " \n", "
\n", " 		\n", "
\n", " \n", " flemarie1@furl.net\n", " \n", "
\n", " 		\n", "
\n", " \n", " Female\n", " \n", "
\n", " 		\n", "
\n", " \n", " 16.145.123.46\n", " \n", "
\n", "
\n", "
\n", " \n", " 2\n", " \n", "
\n", " 		\n", "
\n", " \n", " Fredelia\n", " \n", "
\n", " 		\n", "
\n", " \n", " Lemarie\n", " \n", "
\n", " 		\n", "
\n", " \n", " flemarie1@furl.net\n", " \n", "
\n", " 		\n", "
\n", " \n", " Female\n", " \n", "
\n", " 		\n", "
\n", " \n", " 16.145.123.46\n", " \n", "
\n", "
\n", "
\n", " \n", " 1\n", " \n", "
\n", " 		\n", "
\n", " \n", " Evyn\n", " \n", "
\n", " 		\n", "
\n", " \n", " Abbey\n", " \n", "
\n", " 		\n", "
\n", " \n", " eabbey0@mlb.com\n", " \n", "
\n", " 		\n", "
\n", " \n", " Male\n", " \n", "
\n", " 		\n", "
\n", " \n", " 202.99.246.227\n", " \n", "
\n", "
\n", "\n", "\n", "
Viewing 10 of 10 rows / 6 columns
\n", "
1 partition(s)
\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "db.table_to_df(\"test_data\").table()" ] }, { "cell_type": "code", "execution_count": 73, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES) AS t\n", "INFO:optimus:jdbc:sqlserver://165.227.196.70:1433;databaseName=optimus\n" ] }, { "data": { "text/plain": [ "['test_data']" ] }, "execution_count": 73, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db.tables_names_to_json()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Redshit" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:jdbc:redshift://redshift-cluster-1.chuvgsqx7epn.us-east-1.redshift.amazonaws.com:5439/dev?currentSchema=public\n" ] } ], "source": [ "# Put your db credentials here\n", "db = op.connect(\n", " driver=\"redshift\",\n", " host=\"165.227.196.70\", \n", " database= \"optimus\", \n", " user= \"testuser\", \n", " password = \"test\")" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:(\n", " SELECT relname as table_name,cast (reltuples as integer) AS count \n", " FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) \n", " WHERE nspname IN ('public') AND relkind='r' ORDER BY reltuples DESC) AS t\n", "INFO:optimus:jdbc:redshift://redshift-cluster-1.chuvgsqx7epn.us-east-1.redshift.amazonaws.com:5439/dev?currentSchema=public\n" ] }, { "ename": "Py4JJavaError", "evalue": "An error occurred while calling o41.load.\n: java.sql.SQLException: [Amazon](500150) Error setting/closing connection: Connection timed out: connect.\r\n\tat com.amazon.redshift.client.PGClient.connect(Unknown Source)\r\n\tat com.amazon.redshift.client.PGClient.(Unknown Source)\r\n\tat com.amazon.redshift.core.PGJDBCConnection.connect(Unknown Source)\r\n\tat com.amazon.jdbc.common.BaseConnectionFactory.doConnect(Unknown Source)\r\n\tat com.amazon.jdbc.common.AbstractDriver.connect(Unknown Source)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:63)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:54)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:56)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation.(JDBCRelation.scala:115)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:52)\r\n\tat org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:340)\r\n\tat org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:239)\r\n\tat org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:227)\r\n\tat org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:164)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\r\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\r\n\tat java.lang.reflect.Method.invoke(Method.java:498)\r\n\tat py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)\r\n\tat py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357)\r\n\tat py4j.Gateway.invoke(Gateway.java:282)\r\n\tat py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)\r\n\tat py4j.commands.CallCommand.execute(CallCommand.java:79)\r\n\tat py4j.GatewayConnection.run(GatewayConnection.java:238)\r\nCaused by: com.amazon.support.exceptions.GeneralException: [Amazon](500150) Error setting/closing connection: Connection timed out: connect.\r\n\t... 24 more\r\nCaused by: java.net.ConnectException: Connection timed out: connect\r\n\tat sun.nio.ch.Net.connect0(Native Method)\r\n\tat sun.nio.ch.Net.connect(Net.java:454)\r\n\tat sun.nio.ch.Net.connect(Net.java:446)\r\n\tat sun.nio.ch.SocketChannelImpl.connect(SocketChannelImpl.java:648)\r\n\tat sun.nio.ch.SocketAdaptor.connect(SocketAdaptor.java:96)\r\n\tat com.amazon.redshift.client.PGClient.connect(Unknown Source)\r\n\tat com.amazon.redshift.client.PGClient.(Unknown Source)\r\n\tat com.amazon.redshift.core.PGJDBCConnection.connect(Unknown Source)\r\n\tat com.amazon.jdbc.common.BaseConnectionFactory.doConnect(Unknown Source)\r\n\tat com.amazon.jdbc.common.AbstractDriver.connect(Unknown Source)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:63)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:54)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:56)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation.(JDBCRelation.scala:115)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:52)\r\n\tat org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:340)\r\n\tat org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:239)\r\n\tat org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:227)\r\n\tat org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:164)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\r\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\r\n\tat java.lang.reflect.Method.invoke(Method.java:498)\r\n\tat py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)\r\n\tat py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357)\r\n\tat py4j.Gateway.invoke(Gateway.java:282)\r\n\tat py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)\r\n\tat py4j.commands.CallCommand.execute(CallCommand.java:79)\r\n\tat py4j.GatewayConnection.run(GatewayConnection.java:238)\r\n\tat java.lang.Thread.run(Thread.java:748)\r\n", "output_type": "error", "traceback": [ "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[1;31mPy4JJavaError\u001b[0m Traceback (most recent call last)", "\u001b[1;32m\u001b[0m in \u001b[0;36m\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdb\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtables\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m", "\u001b[1;32m~\\Documents\\Optimus\\optimus\\io\\jdbc.py\u001b[0m in \u001b[0;36mtables\u001b[1;34m(self, schema, database, limit)\u001b[0m\n\u001b[0;32m 179\u001b[0m FROM user_tables ORDER BY table_name\"\"\"\n\u001b[0;32m 180\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 181\u001b[1;33m \u001b[0mdf\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mlimit\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m 182\u001b[0m \u001b[1;32mreturn\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtable\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mlimit\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 183\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n", "\u001b[1;32m~\\Documents\\Optimus\\optimus\\io\\jdbc.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, query, limit)\u001b[0m\n\u001b[0;32m 309\u001b[0m \u001b[0mconf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0moptions\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mtable\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcassandra_table\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkeyspace\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcassandra_keyspace\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 310\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 311\u001b[1;33m \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_limit\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mconf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mload\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mlimit\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m 312\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 313\u001b[0m \u001b[1;32mdef\u001b[0m \u001b[0mdf_to_table\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mtable\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmode\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m\"overwrite\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n", "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pyspark\\sql\\readwriter.py\u001b[0m in \u001b[0;36mload\u001b[1;34m(self, path, format, schema, **options)\u001b[0m\n\u001b[0;32m 170\u001b[0m \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_df\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_jreader\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mload\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_spark\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_sc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_jvm\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mPythonUtils\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtoSeq\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mpath\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 171\u001b[0m \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 172\u001b[1;33m \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_df\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_jreader\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mload\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m 173\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 174\u001b[0m \u001b[1;33m@\u001b[0m\u001b[0msince\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m1.4\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n", "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\py4j\\java_gateway.py\u001b[0m in \u001b[0;36m__call__\u001b[1;34m(self, *args)\u001b[0m\n\u001b[0;32m 1255\u001b[0m \u001b[0manswer\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mgateway_client\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msend_command\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcommand\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 1256\u001b[0m return_value = get_return_value(\n\u001b[1;32m-> 1257\u001b[1;33m answer, self.gateway_client, self.target_id, self.name)\n\u001b[0m\u001b[0;32m 1258\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 1259\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mtemp_arg\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mtemp_args\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n", "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pyspark\\sql\\utils.py\u001b[0m in \u001b[0;36mdeco\u001b[1;34m(*a, **kw)\u001b[0m\n\u001b[0;32m 61\u001b[0m \u001b[1;32mdef\u001b[0m \u001b[0mdeco\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0ma\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 62\u001b[0m \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 63\u001b[1;33m \u001b[1;32mreturn\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0ma\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m 64\u001b[0m \u001b[1;32mexcept\u001b[0m \u001b[0mpy4j\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mprotocol\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mPy4JJavaError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 65\u001b[0m \u001b[0ms\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mjava_exception\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtoString\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n", "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\py4j\\protocol.py\u001b[0m in \u001b[0;36mget_return_value\u001b[1;34m(answer, gateway_client, target_id, name)\u001b[0m\n\u001b[0;32m 326\u001b[0m raise Py4JJavaError(\n\u001b[0;32m 327\u001b[0m \u001b[1;34m\"An error occurred while calling {0}{1}{2}.\\n\"\u001b[0m\u001b[1;33m.\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 328\u001b[1;33m format(target_id, \".\", name), value)\n\u001b[0m\u001b[0;32m 329\u001b[0m \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 330\u001b[0m raise Py4JError(\n", "\u001b[1;31mPy4JJavaError\u001b[0m: An error occurred while calling o41.load.\n: java.sql.SQLException: [Amazon](500150) Error setting/closing connection: Connection timed out: connect.\r\n\tat com.amazon.redshift.client.PGClient.connect(Unknown Source)\r\n\tat com.amazon.redshift.client.PGClient.(Unknown Source)\r\n\tat com.amazon.redshift.core.PGJDBCConnection.connect(Unknown Source)\r\n\tat com.amazon.jdbc.common.BaseConnectionFactory.doConnect(Unknown Source)\r\n\tat com.amazon.jdbc.common.AbstractDriver.connect(Unknown Source)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:63)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:54)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:56)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation.(JDBCRelation.scala:115)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:52)\r\n\tat org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:340)\r\n\tat org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:239)\r\n\tat org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:227)\r\n\tat org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:164)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\r\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\r\n\tat java.lang.reflect.Method.invoke(Method.java:498)\r\n\tat py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)\r\n\tat py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357)\r\n\tat py4j.Gateway.invoke(Gateway.java:282)\r\n\tat py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)\r\n\tat py4j.commands.CallCommand.execute(CallCommand.java:79)\r\n\tat py4j.GatewayConnection.run(GatewayConnection.java:238)\r\nCaused by: com.amazon.support.exceptions.GeneralException: [Amazon](500150) Error setting/closing connection: Connection timed out: connect.\r\n\t... 24 more\r\nCaused by: java.net.ConnectException: Connection timed out: connect\r\n\tat sun.nio.ch.Net.connect0(Native Method)\r\n\tat sun.nio.ch.Net.connect(Net.java:454)\r\n\tat sun.nio.ch.Net.connect(Net.java:446)\r\n\tat sun.nio.ch.SocketChannelImpl.connect(SocketChannelImpl.java:648)\r\n\tat sun.nio.ch.SocketAdaptor.connect(SocketAdaptor.java:96)\r\n\tat com.amazon.redshift.client.PGClient.connect(Unknown Source)\r\n\tat com.amazon.redshift.client.PGClient.(Unknown Source)\r\n\tat com.amazon.redshift.core.PGJDBCConnection.connect(Unknown Source)\r\n\tat com.amazon.jdbc.common.BaseConnectionFactory.doConnect(Unknown Source)\r\n\tat com.amazon.jdbc.common.AbstractDriver.connect(Unknown Source)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:63)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:54)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:56)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation.(JDBCRelation.scala:115)\r\n\tat org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:52)\r\n\tat org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:340)\r\n\tat org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:239)\r\n\tat org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:227)\r\n\tat org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:164)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\r\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\r\n\tat java.lang.reflect.Method.invoke(Method.java:498)\r\n\tat py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)\r\n\tat py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357)\r\n\tat py4j.Gateway.invoke(Gateway.java:282)\r\n\tat py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)\r\n\tat py4j.commands.CallCommand.execute(CallCommand.java:79)\r\n\tat py4j.GatewayConnection.run(GatewayConnection.java:238)\r\n\tat java.lang.Thread.run(Thread.java:748)\r\n" ] } ], "source": [ "db.tables()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "db.table_to_df(\"test_data\").table()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Oracle" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Put your db credentials here\n", "db = op.connect(\n", " driver=\"oracle\",\n", " host=\"165.227.196.70\", \n", " database= \"optimus\", \n", " user= \"testuser\", \n", " password = \"test\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## SQLlite" ] }, { "cell_type": "code", "execution_count": 53, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:jdbc:sqlite:chinook.db\n" ] } ], "source": [ "# Put your db credentials here\n", "db = op.connect(\n", " driver=\"sqlite\",\n", " host=\"chinook.db\", \n", " database= \"employes\", \n", " user= \"testuser\", \n", " password = \"test\")" ] }, { "cell_type": "code", "execution_count": 54, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:(SELECT name FROM sqlite_master WHERE type='table') AS t\n", "INFO:optimus:jdbc:sqlite:chinook.db\n" ] }, { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "
Viewing 10 of 10 rows / 1 columns
\n", "
1 partition(s)
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
name
\n", "
1 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", "
\n", "
\n", " \n", " albums\n", " \n", "
\n", "
\n", "
\n", " \n", " sqlite_sequence\n", " \n", "
\n", "
\n", "
\n", " \n", " artists\n", " \n", "
\n", "
\n", "
\n", " \n", " customers\n", " \n", "
\n", "
\n", "
\n", " \n", " employees\n", " \n", "
\n", "
\n", "
\n", " \n", " genres\n", " \n", "
\n", "
\n", "
\n", " \n", " invoices\n", " \n", "
\n", "
\n", "
\n", " \n", " invoice_items\n", " \n", "
\n", "
\n", "
\n", " \n", " media_types\n", " \n", "
\n", "
\n", "
\n", " \n", " playlists\n", " \n", "
\n", "
\n", "\n", "\n", "
Viewing 10 of 10 rows / 1 columns
\n", "
1 partition(s)
\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "db.tables()" ] }, { "cell_type": "code", "execution_count": 55, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:(SELECT COUNT(*) as COUNT FROM albums) AS t\n", "INFO:optimus:jdbc:sqlite:chinook.db\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "347 rows\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:SELECT * FROM albums\n", "INFO:optimus:(SELECT * FROM albums) AS t\n", "INFO:optimus:jdbc:sqlite:chinook.db\n" ] }, { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "
Viewing 10 of 347 rows / 3 columns
\n", "
1 partition(s)
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
AlbumId
\n", "
1 (int)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
Title
\n", "
2 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
ArtistId
\n", "
3 (int)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", "
\n", "
\n", " \n", " 1\n", " \n", "
\n", " 		\n", "
\n", " \n", " For⋅Those⋅About⋅To⋅Rock⋅We⋅Salute⋅You\n", " \n", "
\n", " 		\n", "
\n", " \n", " 1\n", " \n", "
\n", "
\n", "
\n", " \n", " 2\n", " \n", "
\n", " 		\n", "
\n", " \n", " Balls⋅to⋅the⋅Wall\n", " \n", "
\n", " 		\n", "
\n", " \n", " 2\n", " \n", "
\n", "
\n", "
\n", " \n", " 3\n", " \n", "
\n", " 		\n", "
\n", " \n", " Restless⋅and⋅Wild\n", " \n", "
\n", " 		\n", "
\n", " \n", " 2\n", " \n", "
\n", "
\n", "
\n", " \n", " 4\n", " \n", "
\n", " 		\n", "
\n", " \n", " Let⋅There⋅Be⋅Rock\n", " \n", "
\n", " 		\n", "
\n", " \n", " 1\n", " \n", "
\n", "
\n", "
\n", " \n", " 5\n", " \n", "
\n", " 		\n", "
\n", " \n", " Big⋅Ones\n", " \n", "
\n", " 		\n", "
\n", " \n", " 3\n", " \n", "
\n", "
\n", "
\n", " \n", " 6\n", " \n", "
\n", " 		\n", "
\n", " \n", " Jagged⋅Little⋅Pill\n", " \n", "
\n", " 		\n", "
\n", " \n", " 4\n", " \n", "
\n", "
\n", "
\n", " \n", " 7\n", " \n", "
\n", " 		\n", "
\n", " \n", " Facelift\n", " \n", "
\n", " 		\n", "
\n", " \n", " 5\n", " \n", "
\n", "
\n", "
\n", " \n", " 8\n", " \n", "
\n", " 		\n", "
\n", " \n", " Warner⋅25⋅Anos\n", " \n", "
\n", " 		\n", "
\n", " \n", " 6\n", " \n", "
\n", "
\n", "
\n", " \n", " 9\n", " \n", "
\n", " 		\n", "
\n", " \n", " Plays⋅Metallica⋅By⋅Four⋅Cellos\n", " \n", "
\n", " 		\n", "
\n", " \n", " 7\n", " \n", "
\n", "
\n", "
\n", " \n", " 10\n", " \n", "
\n", " 		\n", "
\n", " \n", " Audioslave\n", " \n", "
\n", " 		\n", "
\n", " \n", " 8\n", " \n", "
\n", "
\n", "\n", "\n", "
Viewing 10 of 347 rows / 3 columns
\n", "
1 partition(s)
\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "db.table_to_df(\"albums\",limit=\"all\").table()" ] }, { "cell_type": "code", "execution_count": 60, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:(SELECT name FROM sqlite_master WHERE type='table') AS t\n", "INFO:optimus:jdbc:sqlite:chinook.db\n" ] }, { "data": { "text/plain": [ "['albums',\n", " 'sqlite_sequence',\n", " 'artists',\n", " 'customers',\n", " 'employees',\n", " 'genres',\n", " 'invoices',\n", " 'invoice_items',\n", " 'media_types',\n", " 'playlists',\n", " 'playlist_track',\n", " 'tracks',\n", " 'sqlite_stat1']" ] }, "execution_count": 60, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db.tables_names_to_json()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Redis" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:Downloading foo.csv from https://raw.githubusercontent.com/ironmussa/Optimus/master/examples/data/foo.csv\n", "INFO:optimus:Downloaded 967 bytes\n", "INFO:optimus:Creating DataFrame for foo.csv. Please wait...\n" ] } ], "source": [ "df = op.load.csv(\"https://raw.githubusercontent.com/ironmussa/Optimus/master/examples/data/foo.csv\", sep=\",\", header='true', infer_schema='true', charset=\"UTF-8\", null_value=\"None\")" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "
Viewing 10 of 19 rows / 8 columns
\n", "
1 partition(s)
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
id
\n", "
1 (int)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
firstName
\n", "
2 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
lastName
\n", "
3 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
billingId
\n", "
4 (int)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
product
\n", "
5 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
price
\n", "
6 (int)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
birth
\n", "
7 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", " 		\n", "
dummyCol
\n", "
8 (string)
\n", "
\n", " \n", " nullable\n", " \n", "
\n", "
\n", "
\n", " \n", " 1\n", " \n", "
\n", " 		\n", "
\n", " \n", " Luis\n", " \n", "
\n", " 		\n", "
\n", " \n", " Alvarez$$%!\n", " \n", "
\n", " 		\n", "
\n", " \n", " 123\n", " \n", "
\n", " 		\n", "
\n", " \n", " Cake\n", " \n", "
\n", " 		\n", "
\n", " \n", " 10\n", " \n", "
\n", " 		\n", "
\n", " \n", " 1980/07/07\n", " \n", "
\n", " 		\n", "
\n", " \n", " never\n", " \n", "
\n", "
\n", "
\n", " \n", " 2\n", " \n", "
\n", " 		\n", "
\n", " \n", " André\n", " \n", "
\n", " 		\n", "
\n", " \n", " Ampère\n", " \n", "
\n", " 		\n", "
\n", " \n", " 423\n", " \n", "
\n", " 		\n", "
\n", " \n", " piza\n", " \n", "
\n", " 		\n", "
\n", " \n", " 8\n", " \n", "
\n", " 		\n", "
\n", " \n", " 1950/07/08\n", " \n", "
\n", " 		\n", "
\n", " \n", " gonna\n", " \n", "
\n", "
\n", "
\n", " \n", " 3\n", " \n", "
\n", " 		\n", "
\n", " \n", " NiELS\n", " \n", "
\n", " 		\n", "
\n", " \n", " Böhr//((%%\n", " \n", "
\n", " 		\n", "
\n", " \n", " 551\n", " \n", "
\n", " 		\n", "
\n", " \n", " pizza\n", " \n", "
\n", " 		\n", "
\n", " \n", " 8\n", " \n", "
\n", " 		\n", "
\n", " \n", " 1990/07/09\n", " \n", "
\n", " 		\n", "
\n", " \n", " give\n", " \n", "
\n", "
\n", "
\n", " \n", " 4\n", " \n", "
\n", " 		\n", "
\n", " \n", " PAUL\n", " \n", "
\n", " 		\n", "
\n", " \n", " dirac$\n", " \n", "
\n", " 		\n", "
\n", " \n", " 521\n", " \n", "
\n", " 		\n", "
\n", " \n", " pizza\n", " \n", "
\n", " 		\n", "
\n", " \n", " 8\n", " \n", "
\n", " 		\n", "
\n", " \n", " 1954/07/10\n", " \n", "
\n", " 		\n", "
\n", " \n", " you\n", " \n", "
\n", "
\n", "
\n", " \n", " 5\n", " \n", "
\n", " 		\n", "
\n", " \n", " Albert\n", " \n", "
\n", " 		\n", "
\n", " \n", " Einstein\n", " \n", "
\n", " 		\n", "
\n", " \n", " 634\n", " \n", "
\n", " 		\n", "
\n", " \n", " pizza\n", " \n", "
\n", " 		\n", "
\n", " \n", " 8\n", " \n", "
\n", " 		\n", "
\n", " \n", " 1990/07/11\n", " \n", "
\n", " 		\n", "
\n", " \n", " up\n", " \n", "
\n", "
\n", "
\n", " \n", " 6\n", " \n", "
\n", " 		\n", "
\n", " \n", " Galileo\n", " \n", "
\n", " 		\n", "
\n", " \n", " ⋅⋅⋅⋅⋅⋅⋅⋅⋅⋅⋅⋅⋅GALiLEI\n", " \n", "
\n", " 		\n", "
\n", " \n", " 672\n", " \n", "
\n", " 		\n", "
\n", " \n", " arepa\n", " \n", "
\n", " 		\n", "
\n", " \n", " 5\n", " \n", "
\n", " 		\n", "
\n", " \n", " 1930/08/12\n", " \n", "
\n", " 		\n", "
\n", " \n", " never\n", " \n", "
\n", "
\n", "
\n", " \n", " 7\n", " \n", "
\n", " 		\n", "
\n", " \n", " CaRL\n", " \n", "
\n", " 		\n", "
\n", " \n", " Ga%%%uss\n", " \n", "
\n", " 		\n", "
\n", " \n", " 323\n", " \n", "
\n", " 		\n", "
\n", " \n", " taco\n", " \n", "
\n", " 		\n", "
\n", " \n", " 3\n", " \n", "
\n", " 		\n", "
\n", " \n", " 1970/07/13\n", " \n", "
\n", " 		\n", "
\n", " \n", " gonna\n", " \n", "
\n", "
\n", "
\n", " \n", " 8\n", " \n", "
\n", " 		\n", "
\n", " \n", " David\n", " \n", "
\n", " 		\n", "
\n", " \n", " H$$$ilbert\n", " \n", "
\n", " 		\n", "
\n", " \n", " 624\n", " \n", "
\n", " 		\n", "
\n", " \n", " taaaccoo\n", " \n", "
\n", " 		\n", "
\n", " \n", " 3\n", " \n", "
\n", " 		\n", "
\n", " \n", " 1950/07/14\n", " \n", "
\n", " 		\n", "
\n", " \n", " let\n", " \n", "
\n", "
\n", "
\n", " \n", " 9\n", " \n", "
\n", " 		\n", "
\n", " \n", " Johannes\n", " \n", "
\n", " 		\n", "
\n", " \n", " KEPLER\n", " \n", "
\n", " 		\n", "
\n", " \n", " 735\n", " \n", "
\n", " 		\n", "
\n", " \n", " taco\n", " \n", "
\n", " 		\n", "
\n", " \n", " 3\n", " \n", "
\n", " 		\n", "
\n", " \n", " 1920/04/22\n", " \n", "
\n", " 		\n", "
\n", " \n", " you\n", " \n", "
\n", "
\n", "
\n", " \n", " 10\n", " \n", "
\n", " 		\n", "
\n", " \n", " JaMES\n", " \n", "
\n", " 		\n", "
\n", " \n", " M$$ax%%well\n", " \n", "
\n", " 		\n", "
\n", " \n", " 875\n", " \n", "
\n", " 		\n", "
\n", " \n", " taco\n", " \n", "
\n", " 		\n", "
\n", " \n", " 3\n", " \n", "
\n", " 		\n", "
\n", " \n", " 1923/03/12\n", " \n", "
\n", " 		\n", "
\n", " \n", " down\n", " \n", "
\n", "
\n", "\n", "\n", "
Viewing 10 of 19 rows / 8 columns
\n", "
1 partition(s)
\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "df.table()" ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [], "source": [ "# Put your db credentials here\n", "db = op.connect(\n", " driver=\"redis\",\n", " host=\"165.227.196.70\", \n", " port = 6379,\n", " database= 1, \n", " password = \"\")" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:optimus:`id`,`firstName`,`lastName`,`billingId`,`product`,`price`,`birth`,`dummyCol` column(s) was not processed because is/are not array,vector\n", "INFO:optimus:Outputting 0 columns after filtering. Is this expected?\n", "INFO:optimus:Using 'column_exp' to process column 'id' with function _cast_to\n", "INFO:optimus:Using 'column_exp' to process column 'firstName' with function _cast_to\n", "INFO:optimus:Using 'column_exp' to process column 'lastName' with function _cast_to\n", "INFO:optimus:Using 'column_exp' to process column 'billingId' with function _cast_to\n", "INFO:optimus:Using 'column_exp' to process column 'product' with function _cast_to\n", "INFO:optimus:Using 'column_exp' to process column 'price' with function _cast_to\n", "INFO:optimus:Using 'column_exp' to process column 'birth' with function _cast_to\n", "INFO:optimus:Using 'column_exp' to process column 'dummyCol' with function _cast_to\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "hola1\n" ] } ], "source": [ "db.df_to_table(df, \"hola1\", redis_primary_key=\"id\")" ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [ { "ename": "TypeError", "evalue": "can only concatenate str (not \"int\") to str", "output_type": "error", "traceback": [ "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[1;31mTypeError\u001b[0m Traceback (most recent call last)", "\u001b[1;32m\u001b[0m in \u001b[0;36m\u001b[1;34m\u001b[0m\n\u001b[0;32m 1\u001b[0m \u001b[1;31m# https://stackoverflow.com/questions/56707978/how-to-write-from-a-pyspark-dstream-to-redis\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 2\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 3\u001b[1;33m \u001b[0mdb\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtable_to_df\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m", "\u001b[1;32m~\\Documents\\Optimus\\optimus\\io\\jdbc.py\u001b[0m in \u001b[0;36mtable_to_df\u001b[1;34m(self, table_name, columns, limit)\u001b[0m\n\u001b[0;32m 122\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 123\u001b[0m \u001b[0mdb_table\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mtable_name\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 124\u001b[1;33m \u001b[0mquery\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdriver_context\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcount_query\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdb_table\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdb_table\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m 125\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0mlimit\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m\"all\"\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 126\u001b[0m \u001b[0mcount\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"all\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfirst\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n", "\u001b[1;32m~\\Documents\\Optimus\\optimus\\io\\driver_context.py\u001b[0m in \u001b[0;36mcount_query\u001b[1;34m(self, *args, **kwargs)\u001b[0m\n\u001b[0;32m 31\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 32\u001b[0m \u001b[1;32mdef\u001b[0m \u001b[0mcount_query\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m->\u001b[0m \u001b[0mstr\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 33\u001b[1;33m \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_driver\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcount_query\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m", "\u001b[1;32m~\\Documents\\Optimus\\optimus\\io\\sqlserver.py\u001b[0m in \u001b[0;36mcount_query\u001b[1;34m(self, *args, **kwargs)\u001b[0m\n\u001b[0;32m 24\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 25\u001b[0m \u001b[1;32mdef\u001b[0m \u001b[0mcount_query\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m->\u001b[0m \u001b[0mstr\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 26\u001b[1;33m \u001b[1;32mreturn\u001b[0m \u001b[1;34m\"SELECT COUNT(*) as COUNT FROM \"\u001b[0m \u001b[1;33m+\u001b[0m \u001b[0mkwargs\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"db_table\"\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m", "\u001b[1;31mTypeError\u001b[0m: can only concatenate str (not \"int\") to str" ] } ], "source": [ "# https://stackoverflow.com/questions/56707978/how-to-write-from-a-pyspark-dstream-to-redis\n", " \n", "db.table_to_df(0)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.7.1" } }, "nbformat": 4, "nbformat_minor": 2 }