.NET for Apache Spark JDBC to connect to Postgresql

[aysegulgurmeric]

I am new on .NET for Apache Spark. I try connect postgresql db. But get these error:JVM method execution failed: Nonstatic method jdbc failed for class 6 when called with 3 arguments

JVM method ex
ecution failed: Nonstatic method load failed for class 6 when called with no arg
uments

DOTNET_WORKER_DIR = C:\bin\Microsoft.Spark.Worker-0.5.0
HADOOP_HOME = C:\bin\spark-2.4.1-bin-hadoop2.7
SPARK_HOME = C:\bin\spark-2.4.1-bin-hadoop2.7
JAVA_HOME = C:\Program Files\Java\jdk1.8.0_221
Dotnet framework version = 2.2
Nuget spark package = "Microsoft.Spark/0.5.0"

%SPARK_HOME%\bin\spark-submit --class org.apache.spark.deploy.dotnet.DotnetRunner --master local
bin\Debug\netcoreapp2.2\microsoft-spark-2.4.x-0.5.0.jar dotnet bin\Debug\netcoreapp2.2\myapp.dll

 var spark = SparkSession
 .Builder().AppName("test_postgresql").GetOrCreate();

    DataFrame frame= spark.Read().Format("jdbc").Option("url", "jdbc:postgresql://host:5432/tablename?user=postgres&password=xxxx")
    .Option("query", "SELECT name FROM public.\"xxxx\"")
    .Load();

Dictionary<string, string> options = new Dictionary<string, string>();
options.Add("ssl", "false");
DataFrame dataFrame2 = spark.Read().Jdbc("jdbc:postgresql://host:port/table?user=postgres&password=xxxx", "Roles", options);

I tried to type of method to connect postgresql db. İs it possible to connect postgresql db ?

Thanks.

When I try to fix problem, I noticed error log about no suitable driver. So I tried to download Microsoft SQL Server JDBC Driver 2.0 and postgresql jdbc driver and put these jar files under to the C:\bin\spark-2.4.1-bin-hadoop2.7\jars folder.

• postgresql-42.2.8.jar
• sqljdbc.jar
• sqljdbc4.jar

And find example of how to connect to postgresql

            DataFrame jdbcDf = spark.Read()
    .Format("jdbc")
    .Options(
        new Dictionary<string, string>
        {
                        {"url", "jdbc:postgresql:192.168.10.198:5432/dbname"},
                        {"dbtable", "Roles"},
                        {"user", "postgres"},
                        {"password", "XXXX"},
                        {"ssl", "false"}
        })
    .Load();

Now I fix "no suitable driver" error with putting jar files but now I take this error :

[Error] [JvmBridge] org.postgresql.ut
il.PSQLException: Connection to localhost:5432 refused. Check that the hostname
and port are correct and that the postmaster is accepting TCP/IP connections.

But I am not trying to connect localhost .

[MikeRys]

This looks like it may be a network setup issue. What Spark engine are you using and where is your Postgres instance running? Have you made sure that all the firewalls are set up to allow the connections? From: aysegulgurmeric <notifications@github.com> Sent: Wednesday, October 23, 2019 3:55 AM To: dotnet/spark <spark@noreply.github.com> Cc: Subscribed <subscribed@noreply.github.com> Subject: [dotnet/spark] .NET for Apache Spark JDBC to connect to Postgresql (#303) I am new on .NET for Apache Spark. I try connect postgresql db. But get these error:JVM method execution failed: Nonstatic method jdbc failed for class 6 when called with 3 arguments JVM method ex ecution failed: Nonstatic method load failed for class 6 when called with no arg uments DOTNET_WORKER_DIR = C:\bin\Microsoft.Spark.Worker-0.5.0 HADOOP_HOME = C:\bin\spark-2.4.1-bin-hadoop2.7 SPARK_HOME = C:\bin\spark-2.4.1-bin-hadoop2.7 JAVA_HOME = C:\Program Files\Java\jdk1.8.0_221 Dotnet framework version = 2.2 Nuget spark package = "Microsoft.Spark/0.5.0" %SPARK_HOME%\bin\spark-submit --class org.apache.spark.deploy.dotnet.DotnetRunner --master local bin\Debug\netcoreapp2.2\microsoft-spark-2.4.x-0.5.0.jar dotnet bin\Debug\netcoreapp2.2\myapp.dll var spark = SparkSession .Builder().AppName("test_postgresql").GetOrCreate(); DataFrame frame= spark.Read().Format("jdbc").Option("url", "jdbc:postgresql://host:5432/tablename?user=postgres&password=xxxx") .Option("query", "SELECT name FROM public.\"xxxx\"") .Load(); Dictionary<string, string> options = new Dictionary<string, string>(); options.Add("ssl", "false"); DataFrame dataFrame2 = spark.Read().Jdbc("jdbc:postgresql://host:port/table?user=postgres&password=xxxx", "Roles", options); I tried to type of method to connect postgresql db. İs it possible to connect postgresql db ? Thanks. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub<https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fdotnet%2Fspark%2Fissues%2F303%3Femail_source%3Dnotifications%26email_token%3DACZXGJBR3POADAJRFL234L3QQAUORA5CNFSM4JD7RAW2YY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4HTY6U7Q&data=02%7C01%7Cmrys%40microsoft.com%7C06ae78cdd13f417bd4d008d757a7632a%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637074248748992000&sdata=AlefJuPdfhpDD5CoYyLweGO8%2F3319AlTGgG6ehI7tYg%3D&reserved=0>, or unsubscribe<https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FACZXGJE565K2MAJKI7QUH53QQAUORANCNFSM4JD7RAWQ&data=02%7C01%7Cmrys%40microsoft.com%7C06ae78cdd13f417bd4d008d757a7632a%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637074248748992000&sdata=02EP%2Bq6veNi15b5bQYcLIVWQbr3S4LgM1YOIe5s6VCc%3D&reserved=0>.

[aysegulgurmeric]

Hi, My spark version is 2.4.1. DOTNET_WORKER_DIR = C:\bin\Microsoft.Spark.Worker-0.5.0 HADOOP_HOME = C:\bin\spark-2.4.1-bin-hadoop2.7 SPARK_HOME = C:\bin\spark-2.4.1-bin-hadoop2.7 JAVA_HOME = C:\Program Files\Java\jdk1.8.0_221 Dotnet framework version = 2.2 Nuget spark package = "Microsoft.Spark/0.5.0" My Postgres instance is running on different server. I can access Postgres instance with pgAdmin from my pc so firewall is not problem. I tried to install spark engine to linux server which my Postgres instance is running (Ubuntu 16.04) according to this guide: https://dotnet.microsoft.com/learn/data/spark-tutorial/run But this is not good as windows version. Environment values are not mentioned. Finally I could run my app and connect to postgresql. I understood the issue. DataFrame jdbcDf = spark.Read() .Format("jdbc") .Options( new Dictionary<string, string> { {"url", "jdbc:postgresql:localhost:5432/dbname"}, {"dbtable", "Roles"}, {"user", "postgres"}, {"password", "xxxxx"}, {"ssl", "false"} }) .Load(); Framework did not accept url value like these: {"url", "jdbc:postgresql:localhost:5432/dbname"}, {"url", "jdbc:postgresql:localhost/dbname"}, Framework accepted url value like this: {"url", "jdbc:postgresql:dbname"}, And it does not accept my value like as I wrote {"dbtable", "Roles"}. It searches dbtable with lower case "roles". I added a table named "roles" then it run. Can you help me please? Michael Rys <notifications@github.com>, 24 Eki 2019 Per, 00:39 tarihinde şunu yazdı:

…

This looks like it may be a network setup issue. What Spark engine are you using and where is your Postgres instance running? Have you made sure that all the firewalls are set up to allow the connections? From: aysegulgurmeric ***@***.***> Sent: Wednesday, October 23, 2019 3:55 AM To: dotnet/spark ***@***.***> Cc: Subscribed ***@***.***> Subject: [dotnet/spark] .NET for Apache Spark JDBC to connect to Postgresql (#303) I am new on .NET for Apache Spark. I try connect postgresql db. But get these error:JVM method execution failed: Nonstatic method jdbc failed for class 6 when called with 3 arguments JVM method ex ecution failed: Nonstatic method load failed for class 6 when called with no arg uments DOTNET_WORKER_DIR = C:\bin\Microsoft.Spark.Worker-0.5.0 HADOOP_HOME = C:\bin\spark-2.4.1-bin-hadoop2.7 SPARK_HOME = C:\bin\spark-2.4.1-bin-hadoop2.7 JAVA_HOME = C:\Program Files\Java\jdk1.8.0_221 Dotnet framework version = 2.2 Nuget spark package = "Microsoft.Spark/0.5.0" %SPARK_HOME%\bin\spark-submit --class org.apache.spark.deploy.dotnet.DotnetRunner --master local bin\Debug\netcoreapp2.2\microsoft-spark-2.4.x-0.5.0.jar dotnet bin\Debug\netcoreapp2.2\myapp.dll var spark = SparkSession .Builder().AppName("test_postgresql").GetOrCreate(); DataFrame frame= spark.Read().Format("jdbc").Option("url", "jdbc:postgresql://host:5432/tablename?user=postgres&password=xxxx") .Option("query", "SELECT name FROM public.\"xxxx\"") .Load(); Dictionary<string, string> options = new Dictionary<string, string>(); options.Add("ssl", "false"); DataFrame dataFrame2 = spark.Read().Jdbc("jdbc:postgresql://host:port/table?user=postgres&password=xxxx", "Roles", options); I tried to type of method to connect postgresql db. İs it possible to connect postgresql db ? Thanks. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub< https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fdotnet%2Fspark%2Fissues%2F303%3Femail_source%3Dnotifications%26email_token%3DACZXGJBR3POADAJRFL234L3QQAUORA5CNFSM4JD7RAW2YY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4HTY6U7Q&data=02%7C01%7Cmrys%40microsoft.com%7C06ae78cdd13f417bd4d008d757a7632a%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637074248748992000&sdata=AlefJuPdfhpDD5CoYyLweGO8%2F3319AlTGgG6ehI7tYg%3D&reserved=0>, or unsubscribe< https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FACZXGJE565K2MAJKI7QUH53QQAUORANCNFSM4JD7RAWQ&data=02%7C01%7Cmrys%40microsoft.com%7C06ae78cdd13f417bd4d008d757a7632a%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637074248748992000&sdata=02EP%2Bq6veNi15b5bQYcLIVWQbr3S4LgM1YOIe5s6VCc%3D&reserved=0>. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#303?email_source=notifications&email_token=ADNFHAWXYYSNRBSOYTKB2OTQQDABLA5CNFSM4JD7RAW2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOECC6Z2Q#issuecomment-545647850>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADNFHARQAY73S27XLEVIQCLQQDABLANCNFSM4JD7RAWQ> .

[suhsteve]

@aysegulgurmeric I believe postgres uses lowercase for table names unless you surround the name with "s. Can you try the following and let me know if it works ?

{"dbtable", "\"Roles\""},

[aysegulgurmeric]

@suhsteve it works as you said. Nevertheless, the main problem is still alive.
The url part is not parsed well.

[suhsteve]

@aysegulgurmeric have you looked at the official spark documentation located here ? In their example they have have an example url jdbc:postgresql://localhost/test?user=fred&password=secret

[rapoth]

@aysegulgurmeric Did you get a chance to try out the suggestion from @suhsteve?

[Niharikadutta]

@aysegulgurmeric Please let us know if you are still facing this issue or were able to get it resolved. Thanks!