Hi everyone, trying to connect to Pinot(single docker container edition). I have to use JDBC. I would like to evaluate how Pinot behaves (DBs, schemas, basic SQL) in either DBeaver or Intellij(Datagrips). I totally failed: • Dbeaver - I created a custom driver, loaded your JAR from Maven. Connection fails, see 1st screenshot • Intellij - see 2nd screenshot Any advice is welcome!

Hi from the error it seems like you are not using the shaded jar but only the driver one. Can you please share the jar name

i have just the repo for you @Jan Soubusta

@Kartik Khare you are right. I searched through Maven, but I didn't find a better driver. @Tim Veil Thanks! I give it a try, but later I will need something more official to use in our production clusters 😉

https://repo1.maven.org/maven2/org/apache/pinot/pinot-jdbc-client/1.0.0/pinot-jdbc-client-1.0.0-shaded.jar this is the official one that should work

Cool, that works, thanks! I tried to SELECT from one of preloaded tables, it seems that Pinot TIMESTAMP data type works differently than in other databases. Any clue how to overcome it?

@Jan Soubusta

apply plugin: 'java'

repositories {
  mavenCentral()
}

dependencies {
  implementation 'org.apache.pinot:pinot-jdbc-client:1.0.0:shaded'
}

check it out ^ Will it work for you?

Does not work:

Execution failed for task ':microservices:sql-executor:kaptGenerateStubsKotlin'.
> Error while evaluating property 'filteredArgumentsMap' of task ':microservices:sql-executor:kaptGenerateStubsKotlin'.
   > Could not resolve all files for configuration ':microservices:sql-executor:compileClasspath'.
      > Could not find pinot-jdbc-client-1.0.0-shared.jar (org.apache.pinot:pinot-jdbc-client:1.0.0).
        Searched in the following locations:
            <https://repo.maven.apache.org/maven2/org/apache/pinot/pinot-jdbc-client/1.0.0/pinot-jdbc-client-1.0.0-shared.jar>

The driver can be downloaded from https://repo1.maven.org/maven2/org/apache/pinot/pinot-jdbc-client/1.0.0/pinot-jdbc-client-1.0.0-shaded.jar. I don't know why my Gradle resolves the base URL differently.

ohhh if you can’t. Just include the unshaded one and along with it also include pinot-common, pinot-spi and pinot dependencies

@Jan Soubusta kindly check your gradle script. jar is

shaded

NOT

shared

Sorry for bothering you with my typos facepalm Now the driver is downloaded correctly, but unfortunately it does not work. Build of my Kotlin microservice reports errors on places which I didn't touch. Based on result of --scan it seems to be caused by a conflict in HikariCP library - it seems that Pinot driver loads an older version, which does not work with our microservice. Before I used the driver differently - I downloaded the JAR and side-loaded it to the microservice in a special way, which resolves such conflicts (we implemented a special code for it). Not sure what I can do with that. Honestly, I am not an expert for Java/Kotlin. I would have to pass it to someone else in our company. Will do it once productization of Pinot integration becomes priority.

@Jan Soubusta, kindly share your project, if it’s possible. Thank you

The code is quite short, so pasting it here. It contains a lot of comments describing the approach. It was successfully used for BigQuery JDBC driver, which contains a lot of conflicts (obsolete libs). Meanwhile we deprecated BigQuery JDBC and implemented a custom integration calling their REST APIs. If also performs better. Anyway, it still works, at least here for Pinot JDBC.

/*
 * (C) 2020 GoodData Corporation
 */
package com.gooddata.tiger.sqlexecutor.db

import java.net.URL
import java.net.URLClassLoader
import java.util.Enumeration

/**
 * DbClassLoader is basically URLClassLoader with reversed order of loading classes. Standard class loading mechanism
 * is to try parent class loader and only if parent cannot load the class current class loader is supposed to load it
 * by itself.
 *
 * DbClassLoader needs to approach class loading in different way. To avoid class definition clashes between loaded
 * drivers and Tiger components we need to isolate driver class graph as much as possible. With this in mind
 * DbClassLoader tries to load classes by itself and only when this fails it falls back to parent class loader.
 */
class DbClassLoader(urls: Array<URL?>) : URLClassLoader(urls, DbClassLoader::class.java.classLoader) {

    /**
     * Tries to load specified class. Implementation uses reversed order for class loading - i.e. instead of trying
     * parent class loader first (to avoid class duplications) it tries to load classes itself and only when the class
     * is not found it falls back to parent class loader.
     */
    override fun loadClass(name: String?, resolve: Boolean): Class<*>? {
        synchronized(getClassLoadingLock(name)) {
            val clazz = findLoadedClass(name)
                ?: findDriverClass(name)
                ?: parent.loadClass(name)
            if (resolve) {
                resolveClass(clazz)
            }
            return clazz
        }
    }

    /**
     * Tries to load the resources. Implementation uses reversed order for the lookup and does not search whole
     * class loader hierarchy - i.e. instead of trying parent class loader first it tries to load resources themselves
     * and only when no resource is found it falls back to the parent class loader.
     */
    override fun getResources(name: String): Enumeration<URL> {
        val dbResources = findResources(name)
        return if (dbResources.hasMoreElements()) dbResources else parent.getResources(name)
    }

    /**
     * Tries to load the resource. Implementation uses reversed order for the lookup and does not search whole
     * class loader hierarchy - i.e. instead of trying parent class loader first it tries to load resource itself and
     * only when the resource is not found it falls back to the parent class loader.
     */
    override fun getResource(name: String): URL? = findResource(name) ?: parent.getResource(name)

    /**
     * Tries to find driver class from managed URL locations. There are some hardcoded exceptions for which we need
     * to fail - e.g. slf4j library.
     */
    private fun findDriverClass(name: String?): Class<*>? =
        if (name?.startsWith("org.slf4j") == true) {
            null
        } else {
            try {
                findClass(name)
            } catch (e: ClassNotFoundException) {
                null
            }
        }
}

@Jan Soubusta Hi, could you share with us the intellij setup to connect to pinot using the jdbc driver? I am not able to do it using https://repo1.maven.org/maven2/org/apache/pinot/pinot-jdbc-client/1.0.0/pinot-jdbc-client-1.0.0-shaded.jar

Hey, I used pinot-jdbc-client-1.1.0-SNAPSHOT-shaded.jar. I set JDBC URL to

jdbc:pinot://<host_name>:443?useMultistageEngine=true

I used Username&password auth. Last time I tried it circa 1 year ago.

I could make it work with the pinot-jdbc-client-xxx-shaded.jar but the url has to be in the form "jdbc:pinot://controller:9000?brokers=broker:8099&useMultistageEngine=true". Thanks for the hints.

Ah, you are right, some deployments require it. I had to use

jdbc:pinot://<host>:443?scheme=https&brokers=<broker_host>