You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Humphrey <hm...@gmail.com> on 2020/10/29 23:20:46 UTC

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/

Re: Inserting date into ignite with spark jdbc

Posted by Humphrey <hm...@gmail.com>.
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/

Re: Inserting date into ignite with spark jdbc

Posted by Vladimir Pligin <vo...@yandex.ru>.
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/

Re: Inserting date into ignite with spark jdbc

Posted by Humphrey <hm...@gmail.com>.
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/

Re: Inserting date into ignite with spark jdbc

Posted by Humphrey <hm...@gmail.com>.
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/

Re: Inserting date into ignite with spark jdbc

Posted by Andrei Aleksandrov <ae...@gmail.com>.
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/

Re: Inserting date into ignite with spark jdbc

Posted by Humphrey <hm...@gmail.com>.
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/

Re: Inserting date into ignite with spark jdbc

Posted by Andrei Aleksandrov <ae...@gmail.com>.
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 
> <aealexsandrov@gmail.com <ma...@gmail.com>> 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>  <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  <http://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/  <http://apache-ignite-users.70518.x6.nabble.com/>
>

Re: Inserting date into ignite with spark jdbc

Posted by Denis Magda <dm...@apache.org>.
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 <ae...@gmail.com>
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> <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/
>
>

Re: Inserting date into ignite with spark jdbc

Posted by Andrei Aleksandrov <ae...@gmail.com>.
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/