Inserting date into ignite with spark jdbc

classic Classic list List threaded Threaded
10 messages Options
Humphrey Humphrey
Reply | Threaded
Open this post in threaded view
|

Inserting date into ignite with spark jdbc

Hello guys this question has been asked on  Stack Overflow
<https://stackoverflow.com/questions/64554684/how-to-create-a-table-with-primary-key-using-jdbc-spark-connector-to-ignite>  
but yet no answer is a provided.

I'm facing the same issue (trying to insert data in ignite using
spark.jdbc):
Exception in thread "main" java.sql.SQLException: No PRIMARY KEY defined for
CREATE TABLE
        at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:1004)

Code:
    println("-- writing using jdbc --")
    val prop = Properties()
    prop["driver"] = "org.apache.ignite.IgniteJdbcThinDriver"

    df.write().apply {
            mode(SaveMode.Overwrite)
            format("jdbc")
            option("url", "jdbc:ignite:thin://127.0.0.1")
            option("dbtable", "comments")
           
option(IgniteDataFrameSettings.OPTION_CREATE_TABLE_PRIMARY_KEY_FIELDS(),
"last_name")
    }.save()

The last option doesn't seem to work/help.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
aealexsandrov aealexsandrov
Reply | Threaded
Open this post in threaded view
|

Re: Inserting date into ignite with spark jdbc

Hi,

Here's an example with correct syntax that should work fine:

 DataFrameWriter < Row > df = resultDF
  .write()
  .format(IgniteDataFrameSettings.FORMAT_IGNITE())
  .option(IgniteDataFrameSettings.OPTION_CONFIG_FILE(), configPath)
  .option(IgniteDataFrameSettings.OPTION_TABLE(), "Person")
  .option(IgniteDataFrameSettings.OPTION_CREATE_TABLE_PRIMARY_KEY_FIELDS(), "id, city_id")
  .option(IgniteDataFrameSettings.OPTION_CREATE_TABLE_PARAMETERS(), "template=partitioned,backups=1")
  .mode(Append);

Please let me know if something is wrong here.

BR,
Andrei

10/30/2020 2:20 AM, Humphrey пишет:
Hello guys this question has been asked on  Stack Overflow
<https://stackoverflow.com/questions/64554684/how-to-create-a-table-with-primary-key-using-jdbc-spark-connector-to-ignite>  
but yet no answer is a provided.

I'm facing the same issue (trying to insert data in ignite using
spark.jdbc):
Exception in thread "main" java.sql.SQLException: No PRIMARY KEY defined for
CREATE TABLE
	at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:1004)

Code:
    println("-- writing using jdbc --")
    val prop = Properties()
    prop["driver"] = "org.apache.ignite.IgniteJdbcThinDriver"

    df.write().apply {
            mode(SaveMode.Overwrite)
            format("jdbc")
            option("url", "jdbc:ignite:thin://127.0.0.1")
            option("dbtable", "comments")
           
option(IgniteDataFrameSettings.OPTION_CREATE_TABLE_PRIMARY_KEY_FIELDS(),
"last_name")
    }.save()

The last option doesn't seem to work/help.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
dmagda dmagda
Reply | Threaded
Open this post in threaded view
|

Re: Inserting date into ignite with spark jdbc

Andrey,

Do we need to update our docs? It feels like the docs miss these details or have an outdated example.

-
Denis


On Fri, Oct 30, 2020 at 7:03 AM Andrei Aleksandrov <[hidden email]> wrote:

Hi,

Here's an example with correct syntax that should work fine:

 DataFrameWriter < Row > df = resultDF
  .write()
  .format(IgniteDataFrameSettings.FORMAT_IGNITE())
  .option(IgniteDataFrameSettings.OPTION_CONFIG_FILE(), configPath)
  .option(IgniteDataFrameSettings.OPTION_TABLE(), "Person")
  .option(IgniteDataFrameSettings.OPTION_CREATE_TABLE_PRIMARY_KEY_FIELDS(), "id, city_id")
  .option(IgniteDataFrameSettings.OPTION_CREATE_TABLE_PARAMETERS(), "template=partitioned,backups=1")
  .mode(Append);

Please let me know if something is wrong here.

BR,
Andrei

10/30/2020 2:20 AM, Humphrey пишет:
Hello guys this question has been asked on  Stack Overflow
<https://stackoverflow.com/questions/64554684/how-to-create-a-table-with-primary-key-using-jdbc-spark-connector-to-ignite>  
but yet no answer is a provided.

I'm facing the same issue (trying to insert data in ignite using
spark.jdbc):
Exception in thread "main" java.sql.SQLException: No PRIMARY KEY defined for
CREATE TABLE
	at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:1004)

Code:
    println("-- writing using jdbc --")
    val prop = Properties()
    prop["driver"] = "org.apache.ignite.IgniteJdbcThinDriver"

    df.write().apply {
            mode(SaveMode.Overwrite)
            format("jdbc")
            option("url", "jdbc:ignite:thin://127.0.0.1")
            option("dbtable", "comments")
           
option(IgniteDataFrameSettings.OPTION_CREATE_TABLE_PRIMARY_KEY_FIELDS(),
"last_name")
    }.save()

The last option doesn't seem to work/help.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
aealexsandrov aealexsandrov
Reply | Threaded
Open this post in threaded view
|

Re: Inserting date into ignite with spark jdbc

Denis,

I can check it out soon. The mentioned problem can probably only be related to JDBC data frames. In this case, I will create a JIRA ticket. But as I know using OPTION_CREATE_TABLE_PRIMARY_KEY_FIELDS should be the same as I showed in my example.

BR,
Andrei

10/30/2020 6:01 PM, Denis Magda пишет:
Andrey,

Do we need to update our docs? It feels like the docs miss these details or have an outdated example.

-
Denis


On Fri, Oct 30, 2020 at 7:03 AM Andrei Aleksandrov <[hidden email]> wrote:

Hi,

Here's an example with correct syntax that should work fine:

 DataFrameWriter < Row > df = resultDF
  .write()
  .format(IgniteDataFrameSettings.FORMAT_IGNITE())
  .option(IgniteDataFrameSettings.OPTION_CONFIG_FILE(), configPath)
  .option(IgniteDataFrameSettings.OPTION_TABLE(), "Person")
  .option(IgniteDataFrameSettings.OPTION_CREATE_TABLE_PRIMARY_KEY_FIELDS(), "id, city_id")
  .option(IgniteDataFrameSettings.OPTION_CREATE_TABLE_PARAMETERS(), "template=partitioned,backups=1")
  .mode(Append);

Please let me know if something is wrong here.

BR,
Andrei

10/30/2020 2:20 AM, Humphrey пишет:
Hello guys this question has been asked on  Stack Overflow
<https://stackoverflow.com/questions/64554684/how-to-create-a-table-with-primary-key-using-jdbc-spark-connector-to-ignite>  
but yet no answer is a provided.

I'm facing the same issue (trying to insert data in ignite using
spark.jdbc):
Exception in thread "main" java.sql.SQLException: No PRIMARY KEY defined for
CREATE TABLE
	at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:1004)

Code:
    println("-- writing using jdbc --")
    val prop = Properties()
    prop["driver"] = "org.apache.ignite.IgniteJdbcThinDriver"

    df.write().apply {
            mode(SaveMode.Overwrite)
            format("jdbc")
            option("url", "jdbc:ignite:thin://127.0.0.1")
            option("dbtable", "comments")
           
option(IgniteDataFrameSettings.OPTION_CREATE_TABLE_PRIMARY_KEY_FIELDS(),
"last_name")
    }.save()

The last option doesn't seem to work/help.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Humphrey Humphrey
Reply | Threaded
Open this post in threaded view
|

Re: Inserting date into ignite with spark jdbc

Yes I don't want to supply an Ignite Configuration XML I would like to
connect through JDBC like any other database. And there is no way to supply
the primary key.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Humphrey Humphrey
Reply | Threaded
Open this post in threaded view
|

Re: Inserting date into ignite with spark jdbc

In reply to this post by aealexsandrov
Let me have a summarize here. Working with
| IgniteDataFrameSettings.OPTION_CONFIG_FILE(), configPath |
seems to work fine.

But when I'm using the JDBC thin client connection, (like connecting to a
database through JDBC Driver) it was giving me the error:
*java.sql.SQLException: No PRIMARY KEY defined for CREATE TABLE* even when
supplying the option *OPTION_CREATE_TABLE_PRIMARY_KEY_FIELDS*.

Do you want me to make a reproducible example? Or is there already a ticket?

Humphrey


aealexsandrov wrote

> Denis,
>
> I can check it out soon. The mentioned problem can probably only be
> related to JDBC data frames. In this case, I will create a JIRA ticket.
> But as I know using OPTION_CREATE_TABLE_PRIMARY_KEY_FIELDS should be the
> same as I showed in my example.
>
> BR,
> Andrei
>
> 10/30/2020 6:01 PM, Denis Magda пишет:
>> Andrey,
>>
>> Do we need to update our docs? It feels like the docs miss these
>> details or have an outdated example.
>>
>> -
>> Denis
>>
>>
>> On Fri, Oct 30, 2020 at 7:03 AM Andrei Aleksandrov
>> &lt;

> aealexsandrov@

>  &lt;mailto:

> aealexsandrov@

> &gt;> wrote:
>>
>>     Hi,
>>
>>     Here's an example with correct syntax that should work fine:
>>
>>     |DataFrameWriter < Row > df = resultDF .write()
>>     .format(IgniteDataFrameSettings.FORMAT_IGNITE())
>>     .option(IgniteDataFrameSettings.OPTION_CONFIG_FILE(), configPath)
>>     .option(IgniteDataFrameSettings.OPTION_TABLE(), "Person")
>>    
>> .option(IgniteDataFrameSettings.OPTION_CREATE_TABLE_PRIMARY_KEY_FIELDS(),
>>     "id, city_id")
>>     .option(IgniteDataFrameSettings.OPTION_CREATE_TABLE_PARAMETERS(),
>>     "template=partitioned,backups=1") .mode(Append); |
>>
>>     Please let me know if something is wrong here.
>>
>>     BR,
>>     Andrei
>>
>>     10/30/2020 2:20 AM, Humphrey пишет:
>>>     Hello guys this question has been asked on  Stack Overflow
>>>    
>>> &lt;https://stackoverflow.com/questions/64554684/how-to-create-a-table-with-primary-key-using-jdbc-spark-connector-to-ignite&gt; 
>>> &lt;https://stackoverflow.com/questions/64554684/how-to-create-a-table-with-primary-key-using-jdbc-spark-connector-to-ignite&gt;   
>>>     but yet no answer is a provided.
>>>
>>>     I'm facing the same issue (trying to insert data in ignite using
>>>     spark.jdbc):
>>>     Exception in thread "main" java.sql.SQLException: No PRIMARY KEY
>>> defined for
>>>     CREATE TABLE
>>>     at
>>>    
>>> org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:1004)
>>>
>>>     Code:
>>>          println("-- writing using jdbc --")
>>>          val prop = Properties()
>>>          prop["driver"] = "org.apache.ignite.IgniteJdbcThinDriver"
>>>
>>>          df.write().apply {
>>>                  mode(SaveMode.Overwrite)
>>>                  format("jdbc")
>>>                  option("url", "jdbc:ignite:thin://127.0.0.1
>>> &lt;http://127.0.0.1&gt;")
>>>                  option("dbtable", "comments")
>>>                
>>>    
>>> option(IgniteDataFrameSettings.OPTION_CREATE_TABLE_PRIMARY_KEY_FIELDS(),
>>>     "last_name")
>>>          }.save()
>>>
>>>     The last option doesn't seem to work/help.
>>>
>>>
>>>
>>>     --
>>>     Sent from:http://apache-ignite-users.70518.x6.nabble.com/ 
>>> &lt;http://apache-ignite-users.70518.x6.nabble.com/&gt;
>>





--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
aealexsandrov aealexsandrov
Reply | Threaded
Open this post in threaded view
|

Re: Inserting date into ignite with spark jdbc

Hi,

It will be great if you share the reproducer.

BR,
Andrei

11/3/2020 10:17 AM, Humphrey пишет:

> Let me have a summarize here. Working with
> | IgniteDataFrameSettings.OPTION_CONFIG_FILE(), configPath |
> seems to work fine.
>
> But when I'm using the JDBC thin client connection, (like connecting to a
> database through JDBC Driver) it was giving me the error:
> *java.sql.SQLException: No PRIMARY KEY defined for CREATE TABLE* even when
> supplying the option *OPTION_CREATE_TABLE_PRIMARY_KEY_FIELDS*.
>
> Do you want me to make a reproducible example? Or is there already a ticket?
>
> Humphrey
>
>
> aealexsandrov wrote
>> Denis,
>>
>> I can check it out soon. The mentioned problem can probably only be
>> related to JDBC data frames. In this case, I will create a JIRA ticket.
>> But as I know using OPTION_CREATE_TABLE_PRIMARY_KEY_FIELDS should be the
>> same as I showed in my example.
>>
>> BR,
>> Andrei
>>
>> 10/30/2020 6:01 PM, Denis Magda пишет:
>>> Andrey,
>>>
>>> Do we need to update our docs? It feels like the docs miss these
>>> details or have an outdated example.
>>>
>>> -
>>> Denis
>>>
>>>
>>> On Fri, Oct 30, 2020 at 7:03 AM Andrei Aleksandrov
>>> &lt;
>> aealexsandrov@
>>   &lt;mailto:
>> aealexsandrov@
>> &gt;> wrote:
>>>      Hi,
>>>
>>>      Here's an example with correct syntax that should work fine:
>>>
>>>      |DataFrameWriter < Row > df = resultDF .write()
>>>      .format(IgniteDataFrameSettings.FORMAT_IGNITE())
>>>      .option(IgniteDataFrameSettings.OPTION_CONFIG_FILE(), configPath)
>>>      .option(IgniteDataFrameSettings.OPTION_TABLE(), "Person")
>>>    
>>> .option(IgniteDataFrameSettings.OPTION_CREATE_TABLE_PRIMARY_KEY_FIELDS(),
>>>      "id, city_id")
>>>      .option(IgniteDataFrameSettings.OPTION_CREATE_TABLE_PARAMETERS(),
>>>      "template=partitioned,backups=1") .mode(Append); |
>>>
>>>      Please let me know if something is wrong here.
>>>
>>>      BR,
>>>      Andrei
>>>
>>>      10/30/2020 2:20 AM, Humphrey пишет:
>>>>      Hello guys this question has been asked on  Stack Overflow
>>>>    
>>>> &lt;https://stackoverflow.com/questions/64554684/how-to-create-a-table-with-primary-key-using-jdbc-spark-connector-to-ignite&gt;
>>>> &lt;https://stackoverflow.com/questions/64554684/how-to-create-a-table-with-primary-key-using-jdbc-spark-connector-to-ignite&gt;
>>>>      but yet no answer is a provided.
>>>>
>>>>      I'm facing the same issue (trying to insert data in ignite using
>>>>      spark.jdbc):
>>>>      Exception in thread "main" java.sql.SQLException: No PRIMARY KEY
>>>> defined for
>>>>      CREATE TABLE
>>>>       at
>>>>    
>>>> org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:1004)
>>>>
>>>>      Code:
>>>>           println("-- writing using jdbc --")
>>>>           val prop = Properties()
>>>>           prop["driver"] = "org.apache.ignite.IgniteJdbcThinDriver"
>>>>
>>>>           df.write().apply {
>>>>                   mode(SaveMode.Overwrite)
>>>>                   format("jdbc")
>>>>                   option("url", "jdbc:ignite:thin://127.0.0.1
>>>> &lt;http://127.0.0.1&gt;")
>>>>                   option("dbtable", "comments")
>>>>                  
>>>>    
>>>> option(IgniteDataFrameSettings.OPTION_CREATE_TABLE_PRIMARY_KEY_FIELDS(),
>>>>      "last_name")
>>>>           }.save()
>>>>
>>>>      The last option doesn't seem to work/help.
>>>>
>>>>
>>>>
>>>>      --
>>>>      Sent from:http://apache-ignite-users.70518.x6.nabble.com/
>>>> &lt;http://apache-ignite-users.70518.x6.nabble.com/&gt;
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Humphrey Humphrey
Reply | Threaded
Open this post in threaded view
|

Re: Inserting date into ignite with spark jdbc

Hello, I made a reproducer  here
<https://github.com/hmmlopez/ignite-spark-jdbc/>  

Can 2 classes to run,
* nl/hlopez/ignitesparkjdbc/server/ServerApplication.kt
* nl/hlopez/ignitesparkjdbc/spark/SparkApplication.kt

ServerApplication starts a Ignite Server Node
SparkApplication starts the spark application that connects to the Ignite
Server.

You can see that method *writeUsingSpringConfig* works but method
*writeUsingJdbc* throws exception.



aealexsandrov wrote
> Hi,
>
> It will be great if you share the reproducer.
>
> BR,
> Andrei





--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Humphrey Humphrey
Reply | Threaded
Open this post in threaded view
|

Re: Inserting date into ignite with spark jdbc

Did anyone had a chance to look at the code?
Should I make a Java one, or is Kotlin good enough?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Vladimir Pligin Vladimir Pligin
Reply | Threaded
Open this post in threaded view
|

Re: Inserting date into ignite with spark jdbc

Hi,

It seems that a dataset internally uses built-in
org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider instead
of org.apache.ignite.spark.impl.IgniteRelationProvider in case you force it
to use JDBC. The provider from spark obviously doesn't tolerate Ignite
custom properties. To be honest I'm sure how it should work, I'll need to
think about it.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/