How to create spark-sql database from jdbc connection

Question

I'm using spark-sql, I want to create queries to join different tables from database.

Apache spark and connection is already working Example:

CREATE TEMPORARY VIEW jdbcTable
USING org.apache.spark.sql.jdbc
OPTIONS (
  url "jdbc:mysql://XXX.XXX.XXX.XXX/mydatabase",
  driver "com.mysql.cj.jdbc.Driver",
  dbtable "mydatabase.mytable1",
  user "XXXX",
  password "xxx"
);
spark-sql> SELECT * FROM jdbcTable;
1       my_data
Time taken: 3.91 seconds, Fetched 1 row(s)

What I need is is something like USE or CREATE mydatabase USING the jdbc connection to be able to run queries with join tables.

Based of the Spark SQL Syntax, I tried the following options:

spark-sql> USE DATABASE mydatabase
         > USING org.apache.spark.sql.jdbc
         > OPTIONS (
         >   url "jdbc:mysql://XXX.XXX.XXX.XXX/mydatabase",
         >   driver "com.mysql.cj.jdbc.Driver",
         >   user "XXXX",
         >   password "xxx"
         > );
Error in query:
mismatched input 'mydatabase' expecting {<EOF>, ';'}(line 1, pos 13)


spark-sql> CREATE DATABASE mydatabase
         > USING org.apache.spark.sql.jdbc
         > OPTIONS (
         >   url "jdbc:mysql://XXX.XXX.XXX.XXX/mydatabase",
         >   driver "com.mysql.cj.jdbc.Driver",
         >   user "XXXX",
         >   password "xxx"
         > );
Error in query:
mismatched input 'USING' expecting {<EOF>, ';'}(line 2, pos 0)

Is there a way to USE or CREATE a database from a jdbc connection in spark-sql command line?

过过招 · Accepted Answer · 2022-03-24 01:13:51Z

1

The answer is no. Refer to spark official documentation:

The Spark SQL CLI is a convenient tool to run the Hive metastore service in local mode and execute queries input from the command line.

It cannot directly connect to external mysql service using JDBC.

answered Mar 24, 2022 at 1:13

过过招

4,3372 gold badges7 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create spark-sql database from jdbc connection

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related