# JDBC writing data to S3 **URL:** \ **Category:** Uncategorized\ **Created:** [May 4, 2021, 1:16pm UTC](https://community.dremio.com/t/jdbc-writing-data-to-s3/7391 "2021-05-04T13:16:52Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Ayush.goyal](https://avatars.discourse-cdn.com/v4/letter/a/c6cbf5/32.png) [@Ayush.goyal](https://community.dremio.com/u/Ayush.goyal)\ **Post date:** [May 4, 2021, 1:16pm UTC](https://community.dremio.com/t/jdbc-writing-data-to-s3/7391/1 "2021-05-04T13:16:53Z")
Hi @balaji.ramaswamy , I am trying to follow create jdbc connection with Dremio using spark . In which I am reading from RDBMS and writing to S3. For that I have already added our s3 as data source with below options. ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/c/ceb382c35ff9cfced29be3a6676e5233a849442d.png) However, When I am trying to write back to data. Below is my code ``` package com.example.dremio.spark; import org.apache.spark.sql.Dataset; import org.apache.spark.sql.Row; import org.apache.spark.sql.SparkSession; import org.apache.spark.sql.functions; public class SparkExample { static Dataset processDf(Dataset orderDetails, Dataset products,Dataset orders){ orders = orders.withColumn("DAY_OF_WEEK",functions.date_format(functions.col("orderDate"),"E")).filter(functions.col("DAY_OF_WEEK").equalTo("Mon")); Dataset order_orderDetails = orders.join(orderDetails,orders.col("orderNumber").equalTo(orderDetails.col("orderNumber")),"inner"); return order_orderDetails.join(products,order_orderDetails.col("productCode").equalTo(products.col("productCode"))); } public static void main(String[] args) throws ClassNotFoundException { SparkSession spark = SparkSession.builder() .appName("Dremio-Spark example") .master("local[*]") .getOrCreate(); Dataset orderDetails = spark.read().format("jdbc").option("driver","com.dremio.jdbc.Driver").option("url", "jdbc:dremio:direct=127.0.0.1:31857").option("dbtable", "mysqldev.classicmodels.orderdetails").option("user", "ayush.goyal").option("password", "mypassword").load(); orderDetails.createOrReplaceGlobalTempView("orderDetails"); Dataset products = spark.read() .format("jdbc") .option("driver","com.dremio.jdbc.Driver") .option("url", "jdbc:dremio:direct=127.0.0.1:31857") .option("dbtable", "mysqldev.classicmodels.products") .option("user", "ayush.goyal") .option("password", "mypassword") .load(); Dataset orders = spark.read() .format("jdbc") .option("driver","com.dremio.jdbc.Driver") .option("url", "jdbc:dremio:direct=127.0.0.1:31857") .option("dbtable", "mysqldev.classicmodels.orders") .option("user", "ayush.goyal") .option("password", "mypassword") .load(); Dataset df = processDf(orderDetails,products,orders); df.show(); df.write().format("jdbc").option("driver","com.dremio.jdbc.Driver").option("url","jdbc:dremio:direct=127.0.0.1:31857").option("dbtable","\"minio-dev\".dremio.result").option("user","ayush.goyal").option("password","mypassword").save(); } } ``` With this code I am to read the data but unable to write because Dremio interpreting this incorrectly. for that I have attached the profile. [ac3a7993-131c-4fad-bbee-5452ea7cc240.zip](https://community.dremio.com/uploads/short-url/bc78fqkhyM4SfyeHclYtdSkpdKe.zip) (5.7 KB) Thanks ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [May 5, 2021, 7:17am UTC](https://community.dremio.com/t/jdbc-writing-data-to-s3/7391/2 "2021-05-05T07:17:31Z")
@Ayush.goyal Dremio only supports CTAS not just create table, here are some examples [https://docs.dremio.com/sql-reference/sql-commands/tables.html](https://docs.dremio.com/sql-reference/sql-commands/tables.html) ---
**Author:** ![Ayush.goyal](https://avatars.discourse-cdn.com/v4/letter/a/c6cbf5/32.png) [@Ayush.goyal](https://community.dremio.com/u/Ayush.goyal)\ **Post date:** [May 5, 2021, 7:27am UTC](https://community.dremio.com/t/jdbc-writing-data-to-s3/7391/3 "2021-05-05T07:27:54Z")
Hi @balaji.ramaswamy , I am not running query directly. I am using spark to write the data to s3 source through dremio. Is it possible write data to s3 source? if yes then how? ---
**Author:** ![Ayush.goyal](https://avatars.discourse-cdn.com/v4/letter/a/c6cbf5/32.png) [@Ayush.goyal](https://community.dremio.com/u/Ayush.goyal)\ **Post date:** [May 6, 2021, 4:24am UTC](https://community.dremio.com/t/jdbc-writing-data-to-s3/7391/4 "2021-05-06T04:24:42Z")
Hi @balaji.ramaswamy , Is there a way to programatically write data to s3 source by enabling CTAS and Drop queries in advance systems? ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [May 6, 2021, 6:55am UTC](https://community.dremio.com/t/jdbc-writing-data-to-s3/7391/5 "2021-05-06T06:55:52Z")
@Ayush.goyal You can use REST API calls to Create source [http://docs.dremio.com/rest-api/catalog/container-source/](http://docs.dremio.com/rest-api/catalog/container-source/) Modify source [http://docs.dremio.com/rest-api/catalog/container-source-config/](http://docs.dremio.com/rest-api/catalog/container-source-config/) SQL API [http://docs.dremio.com/rest-api/sql/post-sql/](http://docs.dremio.com/rest-api/sql/post-sql/) ---
**Author:** ![Ayush.goyal](https://avatars.discourse-cdn.com/v4/letter/a/c6cbf5/32.png) [@Ayush.goyal](https://community.dremio.com/u/Ayush.goyal)\ **Post date:** [May 6, 2021, 1:28pm UTC](https://community.dremio.com/t/jdbc-writing-data-to-s3/7391/6 "2021-05-06T13:28:56Z")
@balaji.ramaswamy I am more interested in creation of file programatically using jdbc/odbc drivers. ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [May 7, 2021, 6:37am UTC](https://community.dremio.com/t/jdbc-writing-data-to-s3/7391/7 "2021-05-07T06:37:15Z")
@Ayush.goyal Apologies, not able to fully understand your question, let me try to understand and help. Are you trying to run a CTAS from JDBC program or tool? Not sure how you can create CTAS files from the driver? I assume when you say JDBC then it is via a JDBC program (That internally uses the driver)? ---
**Author:** ![Ayush.goyal](https://avatars.discourse-cdn.com/v4/letter/a/c6cbf5/32.png) [@Ayush.goyal](https://community.dremio.com/u/Ayush.goyal)\ **Post date:** [May 7, 2021, 6:51am UTC](https://community.dremio.com/t/jdbc-writing-data-to-s3/7391/8 "2021-05-07T06:51:13Z")
Yes, I am trying to create file from a dataset in Spark using JDBC. Generally this command from my code create file on destination with data in dataset. This is the way we use spark to write to jdbc source. Is it different with dremio. If yes, then can you please share the example to do that? ``` df.write().format("jdbc").option("driver","com.dremio.jdbc.Driver").option("url","jdbc:dremio:direct=127.0.0.1:31857").option("dbtable","\"minio-dev\".dremio.result").option("user","ayush.goyal").option("password","mypassword").save(); ``` ---
**Author:** ![Ayush.goyal](https://avatars.discourse-cdn.com/v4/letter/a/c6cbf5/32.png) [@Ayush.goyal](https://community.dremio.com/u/Ayush.goyal)\ **Post date:** [May 12, 2021, 4:10am UTC](https://community.dremio.com/t/jdbc-writing-data-to-s3/7391/9 "2021-05-12T04:10:24Z")
Hi @balaji.ramaswamy Any info on this? Is Dremio support writing to data lake using jdbc connector? ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [May 12, 2021, 6:55am UTC](https://community.dremio.com/t/jdbc-writing-data-to-s3/7391/10 "2021-05-12T06:55:10Z")
@Ayush.goyal Currently the only way to write to S3 is using the CTAS command, it is a Dremio command that can be called from the UI or a JDBC tool like DBeaver/DBvisualizer