Re: Create table before inserting in SQL

[Harut Martirosyan]

Thank you very much.

I understand the performance implications and that Spark will download it 
before modifying. 
The JDBC database is just extremely small, it’s the BI/aggregated layer.

What’s interesting is that here it says I can use JDBC

https://spark.apache.org/docs/3.3.1/sql-ref-syntax-dml-insert-overwrite-directory.html

But when I try to I get an error that the underlying datastore should be file 
based, I guess a documentation mistake.

Thank you one more time.

> On 2 Feb 2023, at 23:11, Mich Talebzadeh  wrote:
> 
> Please bear in mind that insert/update delete operations are DML, whereas 
> CREATE/DROP TABLE are DDL operations that are best performed in the native 
> database which I presume is a transactional.
> 
> Can you CREATE TABLE before (any insert of data) using the native JDBC 
> database syntax?
> 
> Alternatively you may be able to do so in Python or SCALA but I don't know 
> the way in pure SQL.
> 
> if your JDBC database is Hive you can do so easily
> 
> HTH
> 
> 
> 
>view my Linkedin profile 
> 
> 
>  https://en.everybodywiki.com/Mich_Talebzadeh
> 
>  
> Disclaimer: Use it at your own risk. Any and all responsibility for any loss, 
> damage or destruction of data or any other property which may arise from 
> relying on this email's technical content is explicitly disclaimed. The 
> author will in no case be liable for any monetary damages arising from such 
> loss, damage or destruction.
>  
> 
> 
> On Thu, 2 Feb 2023 at 17:26, Harut Martirosyan  > wrote:
>> Generally, the problem is that I don’t find a way to automatically create a 
>> JDBC table in the JDBC database when I want to insert data into it using 
>> Spark SQL only, not DataFrames API.
>> 
>>> On 2 Feb 2023, at 21:22, Harut Martirosyan >> > wrote:
>>> 
>>> Hi, thanks for the reply.
>>> 
>>> Let’s imagine we have a parquet based table called parquet_table, now I 
>>> want to insert it into a new JDBC table, all using pure SQL.
>>> 
>>> If the JDBC table already exists, it’s easy, we do CREATE TABLE USING JDBC 
>>> and then we do INSERT INTO that table.
>>> 
>>> If the table doesn’t exist, is there a way to create it using Spark SQL 
>>> only? I don’t want to use DataFrames API, I know that I can use .write() 
>>> for that, but I want to keep it in pure SQL, since that is more 
>>> comprehendible for data analysts.
>>> 
 On 2 Feb 2023, at 02:08, Mich Talebzadeh >>> > wrote:
 
 Hi,
 
 It is not very clear your statement below:
 
 ".. If the table existed, I would create a table using JDBC in spark SQL 
 and then insert into it, but I can't create a table if it doesn't exist in 
 JDBC database..."
 
 If the table exists in your JDBC database, why do you need to create it?
 
 How do you verify if it exists? Can you share the code and the doc link?
 
 HTH
 
 
 
view my Linkedin profile 
 
 
  https://en.everybodywiki.com/Mich_Talebzadeh
 
  
 Disclaimer: Use it at your own risk. Any and all responsibility for any 
 loss, damage or destruction of data or any other property which may arise 
 from relying on this email's technical content is explicitly disclaimed. 
 The author will in no case be liable for any monetary damages arising from 
 such loss, damage or destruction.
  
 
 
 On Wed, 1 Feb 2023 at 19:33, Harut Martirosyan 
 mailto:harut.martiros...@gmail.com>> wrote:
> I have a resultset (defined in SQL), and I want to insert it into my JDBC 
> database using only SQL, not dataframes API.
> 
> If the table existed, I would create a table using JDBC in spark SQL and 
> then insert into it, but I can't create a table if it doesn't exist in 
> JDBC database.
> 
> How to do that using pure SQL (no python/scala/java)?
> 
> I am trying to use INSERT OVERWRITE DIRECTORY with JDBC file format 
> (according to the documentation) but as expected this functionality is 
> available only for File-based storage systems.
> 
> -- 
> RGRDZ Harut
>>> 
>> 

Re: Create table before inserting in SQL

[Mich Talebzadeh]

Please bear in mind that insert/update delete operations are DML,
whereas CREATE/DROP TABLE are DDL operations that are best performed in the
native database which I presume is a transactional.

Can you CREATE TABLE before (any insert of data) using the native JDBC
database syntax?

Alternatively you may be able to do so in Python or SCALA but I don't know
the way in pure SQL.

if your JDBC database is Hive you can do so easily

HTH



   view my Linkedin profile



 https://en.everybodywiki.com/Mich_Talebzadeh



*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.




On Thu, 2 Feb 2023 at 17:26, Harut Martirosyan 
wrote:

> Generally, the problem is that I don’t find a way to automatically create
> a JDBC table in the JDBC database when I want to insert data into it using
> Spark SQL only, not DataFrames API.
>
> On 2 Feb 2023, at 21:22, Harut Martirosyan 
> wrote:
>
> Hi, thanks for the reply.
>
> Let’s imagine we have a parquet based table called parquet_table, now I
> want to insert it into a new JDBC table, all using pure SQL.
>
> If the JDBC table already exists, it’s easy, we do CREATE TABLE USING JDBC
> and then we do INSERT INTO that table.
>
> If the table doesn’t exist, is there a way to create it using Spark SQL
> only? I don’t want to use DataFrames API, I know that I can use .write()
> for that, but I want to keep it in pure SQL, since that is more
> comprehendible for data analysts.
>
> On 2 Feb 2023, at 02:08, Mich Talebzadeh 
> wrote:
>
> Hi,
>
> It is not very clear your statement below:
>
> ".. If the table existed, I would create a table using JDBC in spark SQL
> and then insert into it, but I can't create a table if it doesn't exist in
> JDBC database..."
>
> If the table exists in your JDBC database, why do you need to create it?
>
> How do you verify if it exists? Can you share the code and the doc link?
>
> HTH
>
>
>
>view my Linkedin profile
> 
>
>
>  https://en.everybodywiki.com/Mich_Talebzadeh
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>
>
> On Wed, 1 Feb 2023 at 19:33, Harut Martirosyan <
> harut.martiros...@gmail.com> wrote:
>
>> I have a resultset (defined in SQL), and I want to insert it into my JDBC
>> database using only SQL, not dataframes API.
>>
>> If the table existed, I would create a table using JDBC in spark SQL and
>> then insert into it, but I can't create a table if it doesn't exist in JDBC
>> database.
>>
>> How to do that using pure SQL (no python/scala/java)?
>>
>> I am trying to use INSERT OVERWRITE DIRECTORY with JDBC file format
>> (according to the documentation) but as expected this functionality is
>> available only for File-based storage systems.
>>
>> --
>> RGRDZ Harut
>>
>
>
>

Re: Create table before inserting in SQL

[Harut Martirosyan]

Generally, the problem is that I don’t find a way to automatically create a 
JDBC table in the JDBC database when I want to insert data into it using Spark 
SQL only, not DataFrames API.

> On 2 Feb 2023, at 21:22, Harut Martirosyan  
> wrote:
> 
> Hi, thanks for the reply.
> 
> Let’s imagine we have a parquet based table called parquet_table, now I want 
> to insert it into a new JDBC table, all using pure SQL.
> 
> If the JDBC table already exists, it’s easy, we do CREATE TABLE USING JDBC 
> and then we do INSERT INTO that table.
> 
> If the table doesn’t exist, is there a way to create it using Spark SQL only? 
> I don’t want to use DataFrames API, I know that I can use .write() for that, 
> but I want to keep it in pure SQL, since that is more comprehendible for data 
> analysts.
> 
>> On 2 Feb 2023, at 02:08, Mich Talebzadeh  wrote:
>> 
>> Hi,
>> 
>> It is not very clear your statement below:
>> 
>> ".. If the table existed, I would create a table using JDBC in spark SQL and 
>> then insert into it, but I can't create a table if it doesn't exist in JDBC 
>> database..."
>> 
>> If the table exists in your JDBC database, why do you need to create it?
>> 
>> How do you verify if it exists? Can you share the code and the doc link?
>> 
>> HTH
>> 
>> 
>> 
>>view my Linkedin profile 
>> 
>> 
>>  https://en.everybodywiki.com/Mich_Talebzadeh
>> 
>>  
>> Disclaimer: Use it at your own risk. Any and all responsibility for any 
>> loss, damage or destruction of data or any other property which may arise 
>> from relying on this email's technical content is explicitly disclaimed. The 
>> author will in no case be liable for any monetary damages arising from such 
>> loss, damage or destruction.
>>  
>> 
>> 
>> On Wed, 1 Feb 2023 at 19:33, Harut Martirosyan > > wrote:
>>> I have a resultset (defined in SQL), and I want to insert it into my JDBC 
>>> database using only SQL, not dataframes API.
>>> 
>>> If the table existed, I would create a table using JDBC in spark SQL and 
>>> then insert into it, but I can't create a table if it doesn't exist in JDBC 
>>> database.
>>> 
>>> How to do that using pure SQL (no python/scala/java)?
>>> 
>>> I am trying to use INSERT OVERWRITE DIRECTORY with JDBC file format 
>>> (according to the documentation) but as expected this functionality is 
>>> available only for File-based storage systems.
>>> 
>>> -- 
>>> RGRDZ Harut
> 

Re: Create table before inserting in SQL

[Harut Martirosyan]

Hi, thanks for the reply.

Let’s imagine we have a parquet based table called parquet_table, now I want to 
insert it into a new JDBC table, all using pure SQL.

If the JDBC table already exists, it’s easy, we do CREATE TABLE USING JDBC and 
then we do INSERT INTO that table.

If the table doesn’t exist, is there a way to create it using Spark SQL only? I 
don’t want to use DataFrames API, I know that I can use .write() for that, but 
I want to keep it in pure SQL, since that is more comprehendible for data 
analysts.

> On 2 Feb 2023, at 02:08, Mich Talebzadeh  wrote:
> 
> Hi,
> 
> It is not very clear your statement below:
> 
> ".. If the table existed, I would create a table using JDBC in spark SQL and 
> then insert into it, but I can't create a table if it doesn't exist in JDBC 
> database..."
> 
> If the table exists in your JDBC database, why do you need to create it?
> 
> How do you verify if it exists? Can you share the code and the doc link?
> 
> HTH
> 
> 
> 
>view my Linkedin profile 
> 
> 
>  https://en.everybodywiki.com/Mich_Talebzadeh
> 
>  
> Disclaimer: Use it at your own risk. Any and all responsibility for any loss, 
> damage or destruction of data or any other property which may arise from 
> relying on this email's technical content is explicitly disclaimed. The 
> author will in no case be liable for any monetary damages arising from such 
> loss, damage or destruction.
>  
> 
> 
> On Wed, 1 Feb 2023 at 19:33, Harut Martirosyan  > wrote:
>> I have a resultset (defined in SQL), and I want to insert it into my JDBC 
>> database using only SQL, not dataframes API.
>> 
>> If the table existed, I would create a table using JDBC in spark SQL and 
>> then insert into it, but I can't create a table if it doesn't exist in JDBC 
>> database.
>> 
>> How to do that using pure SQL (no python/scala/java)?
>> 
>> I am trying to use INSERT OVERWRITE DIRECTORY with JDBC file format 
>> (according to the documentation) but as expected this functionality is 
>> available only for File-based storage systems.
>> 
>> -- 
>> RGRDZ Harut

Re: Create table before inserting in SQL

[Mich Talebzadeh]

Hi,

It is not very clear your statement below:

".. If the table existed, I would create a table using JDBC in spark SQL
and then insert into it, but I can't create a table if it doesn't exist in
JDBC database..."


If the table exists in your JDBC database, why do you need to create it?


How do you verify if it exists? Can you share the code and the doc link?


HTH




   view my Linkedin profile



 https://en.everybodywiki.com/Mich_Talebzadeh



*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.




On Wed, 1 Feb 2023 at 19:33, Harut Martirosyan 
wrote:

> I have a resultset (defined in SQL), and I want to insert it into my JDBC
> database using only SQL, not dataframes API.
>
> If the table existed, I would create a table using JDBC in spark SQL and
> then insert into it, but I can't create a table if it doesn't exist in JDBC
> database.
>
> How to do that using pure SQL (no python/scala/java)?
>
> I am trying to use INSERT OVERWRITE DIRECTORY with JDBC file format
> (according to the documentation) but as expected this functionality is
> available only for File-based storage systems.
>
> --
> RGRDZ Harut
>

Create table before inserting in SQL

[Harut Martirosyan]

I have a resultset (defined in SQL), and I want to insert it into my JDBC
database using only SQL, not dataframes API.

If the table existed, I would create a table using JDBC in spark SQL and
then insert into it, but I can't create a table if it doesn't exist in JDBC
database.

How to do that using pure SQL (no python/scala/java)?

I am trying to use INSERT OVERWRITE DIRECTORY with JDBC file format
(according to the documentation) but as expected this functionality is
available only for File-based storage systems.

-- 
RGRDZ Harut