Trying to Get Lookups working

TROUBLESHOOTING Google Groups
#1

I am attempting to create a lookup, that will ‘backed’ by a mysql database.

What I have done so far:

  1. Updated configs (all nodes) (common.runtime.propeties) to match:

druid.extensions.loadList=[“druid-parser-route”,“druid-s3-extension
s”,“druid-kafka-indexing-service”,“druid-lookups-cached-global”]

restarted all nodes:

  1. Sent a post to:

http://uswest2-dev-aldruidmaster-001.aws-dev:8081/druid/coordinator/v1/lookups/config

with empty payload {}

  1. Sent a post to:

http://uswest2-dev-aldruidmaster-001.aws-dev:8081/druid/coordinator/v1/lookups/config

with payload:

{
“__default”: {
“carriernamebyid”: {
“version”: “v0”,
“lookupExtractorFactory”: {
“type”: “cachedNamespace”,
“extractionNamespace”: {
“type”: “jdbc”,
“connectorConfig”: {
“createTables”: true,
“connectURI”: “jdbc:mysql://uswest2-dev-almisc-001.aws-dev:3306/meta”,
“user”: “myuser”,
“password”: “mypass”
},
“table”: “carrier”,
“keyColumn”: “carrierid”,
“valueColumn”: “name”,
“pollPeriod”:600000
},
“firstCacheTimeout”: 120000,
“injective”: false
}
}
}
}

  1. then attempted a query:

select count(*),carriername,LOOKUP(‘carrierid’,‘carriernamebyid’) from sms_detail
where __time >= ‘2019-02-01 00:00:00’
and __time < ‘2019-02-01 00:15:00’
group by carriername;

WHICH RESULTS IN:

carriername=$0,EXPR$2=LOOKUP(‘carrierid’, ‘carriernamebyid’))] -> NullPointerException: Lookup [carriernamebyid] not found
SQLState: 00000
ErrorCode: -1

so…thinking maybe the ‘first’ time posted by lookup…it didn’t take…I do it again…but…this time I get:

“error”: “given update for lookup [__default]:[carriernamebyid] can’t replace existing spec [LookupExtractorFactoryContainer{version=‘v0’, lookupExtractorFactory={type=cachedNamespace, extractionNamespace={type=jdbc, connectorConfig={createTables=true, connectURI=jdbc:mysql://uswest2-dev-almisc-001.aws-dev:3306/meta, user=admin, password=admin}, table=carrier, keyColumn=carrierid, valueColumn=name, pollPeriod=600000}, firstCacheTimeout=120000, injective=true}}].”
}

So…I then did an update using ‘v1’ (removing the injective=true…(since its not)
in place but the query continues to fail with the same error…

so not sure…if my lookup is defined incorrectly? or?

suggestions?

#2

note: when I call:

GET: http://uswest2-dev-aldruidmaster-001.aws-dev:8081/druid/coordinator/v1/lookups/config/all

my lookup above is returned, so I know it ‘exists’

if I call: GET http://uswest2-dev-aldruidmaster-001.aws-dev:8081/druid/coordinator/v1/lookups/config/_default/carriernamebyid

I get a 404…which feels wrong…

#3

poking around some more in the logs:

I see (in the broker log)

2019-06-18T10:20:41,120 ERROR [NamespaceExtractionCacheManager-1] org.apache.druid.server.lookup.namespace.cache.CacheScheduler - Failed to update namespace [JdbcExtractionNamespace{connectorConfig=DbConnectorConfig{createTables=true, connectURI=‘jdbc:mysql://uswest2-dev-almisc-001.aws-dev:3306/meta’, user=‘admin’, passwordProvider=org.apache.druid.metadata.DefaultPasswordProvider, dbcpProperties=null}, table=‘carrier’, keyColumn=‘carrierId’, valueColumn=‘name’, tsColumn=‘null’, filter=‘null’, pollPeriod=PT10M}] : org.apache.druid.server.lookup.namespace.cache.CacheScheduler$EntryImpl@14fa4a8e

org.skife.jdbi.v2.exceptions.UnableToObtainConnectionException: java.sql.SQLException: No suitable driver found for jdbc:mysql://uswest2-dev-almisc-001.aws-dev:3306/meta
at org.skife.jdbi.v2.DBI.open(DBI.java:230) ~[jdbi-2.63.1.jar:2.63.1]
at org.skife.jdbi.v2.DBI.withHandle(DBI.java:279) ~[jdbi-2.63.1.jar:2.63.1]
at org.apache.druid.server.lookup.namespace.JdbcCacheGenerator.generateCache(JdbcCacheGenerator.java:77) ~[druid-lookups-cached-global-0.14.0-incubating-iap10.jar:0.14.0-incubating-iap10]
at org.apache.druid.server.lookup.namespace.JdbcCacheGenerator.generateCache(JdbcCacheGenerator.java:49) ~[druid-lookups-cached-global-0.14.0-incubating-iap10.jar:0.14.0-incubating-iap10]
at org.apache.druid.server.lookup.namespace.cache.CacheScheduler$EntryImpl.tryUpdateCache(CacheScheduler.java:229) [druid-lookups-cached-global-0.14.0-incubating-iap10.jar:0.14.0-incubating-iap10]

so…I’m guessing I need to somehow add the mysql/mariadb drivers to my distro? (for my ‘test cluster’ I am simply using derby…for the meta repo)…

is there a doc anywhere on how/where I just be dropping the mysql/mariadb jdbc driver?

#4

Hi Daniel,

Can you check the load status of lookups using http://coordinator_host:port/druid/coordinator/v1/lookups/status/{your_tier_name}/ ?
Replace {your_tier_name} with your tier name. Alternatively you can skip giving the tier name, just invoke this GET API http://coordinator_host:port/druid/coordinator/v1/lookups/status/.

If the loadstatus for your lookup is false looking at historical logs for lookup related errors will help you find the issue.

Thanks,

Sashi

#5

Copy mysql-connector-java jar into <druid_home>/dist/druid/lib directory. You might have to restart the Historicals after copying the jar.

#6

Some progress:

  1. I did as suggested, and copy the mysql connector to jar into the libs folder on my query nodes/data nodes, restarted all nodes.

  2. now when i send query however it get:

2019-06-18T13:00:13,621 WARN [qtp272220100-164] org.eclipse.jetty.server.HttpChannel - /druid/v2/sql/avatica/
java.lang.AssertionError: Type mismatch:
rowtype of new rel:
RecordType(BIGINT NOT NULL EXPR$0, VARCHAR CHARACTER SET “UTF-16LE” COLLATE “UTF-16LE$en_US$primary” carriername, VARCHAR CHARACTER SET “UTF-16LE” COLLATE “UTF-16LE$en_US$primary” EXPR$2) NOT NULL
rowtype of set:
RecordType(BIGINT NOT NULL EXPR$0, VARCHAR CHARACTER SET “UTF-16LE” COLLATE “UTF-16LE$en_US$primary” carriername, VARCHAR CHARACTER SET “UTF-16LE” COLLATE “UTF-16LE$en_US$primary” NOT NULL EXPR$2) NOT NULL
at org.apache.calcite.util.Litmus$1.fail(Litmus.java:31) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.calcite.plan.RelOptUtil.equal(RelOptUtil.java:1857) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.calcite.plan.volcano.RelSubset.add(RelSubset.java:276) ~[calcite-core-1.17.0-iap1.jar

Seeing as this looks like a charset…issue…I tried recreating the table using utf16-le, but I am only able to create it as utf16…doesn’t appear to support utf16-le (apparently centos pulled down 5.5.6, instead of 10.x…I believe 10.x support utf16le)

also calling:http://uswest2-dev-aldruidmaster-001.aws-dev:8081/druid/coordinator/v1/lookups/status

shows:

{
“__default”: {
“carriernamebyid”: {
“loaded”: true
}
}
}

I will see if upgrading mariadb to 10+ with utf16le…solves the issue…(this really should be better documented somewhere).

#7

ok, so now i have updated to mariadb 10 with utf16le, but still getting the same issue…

so now…looking closer I see:

–> RecordType(BIGINT NOT NULL EXPR$0

indeed in the mysql database:

carrierid is a BIGINT,but in druid the field is VARCHAR

so…my question then is…do the types need to match?

i.e. my lookup:

},
“table”: “carrier”,
“keyColumn”: “carrierid”, <–BIGINT in mysql, but in druid its a varchar.
“valueColumn”: “name”,
“pollPeriod”:600000

I have tried:

select count(*),carriername,LOOKUP(CAST(‘carrierid’ as BIGINT),‘carriernamebyid’) from sms_detail
where __time >= ‘2019-02-01 00:00:00’
and __time < ‘2019-02-01 00:15:00’
group by carriername;

basically the mysql table is:

carrierid BIGINT, name varchar

in druid:

carrierid string

thoughts?

#8

Ok so Now I’m confused.

  1. I deleted all my lookups

  2. I delete my database table (that had a BIGINT field)

  3. I recreated the table all columns are now varchars, and I reloaded the data into that varchar table.

  4. I recreated the lookup

  5. Now when I do a query referencing my lookup, the error log on the broker is still showing RecordType(‘bigint’…)…so perhaps this has nothing to do with the type in the table?? or…?

2019-06-18T14:25:58,830 WARN [qtp272220100-145] org.eclipse.jetty.server.HttpChannel - /druid/v2/sql/avatica/
java.lang.AssertionError: Type mismatch:
rowtype of new rel:
RecordType(BIGINT NOT NULL EXPR$0, VARCHAR CHARACTER SET “UTF-16LE” COLLATE “UTF-16LE$en_US$primary” carriername, VARCHAR CHARACTER SET “UTF-16LE” COLLATE “UTF-16LE$en_US$primary” EXPR$2) NOT NULL
rowtype of set:
RecordType(BIGINT NOT NULL EXPR$0, VARCHAR CHARACTER SET “UTF-16LE” COLLATE “UTF-16LE$en_US$primary” carriername, VARCHAR CHARACTER SET “UTF-16LE” COLLATE “UTF-16LE$en_US$primary” NOT NULL EXPR$2) NOT NULL
at org.apache.calcite.util.Litmus$1.fail(Litmus.java:31) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.calcite.plan.RelOp

#9

ok…I have made some progress :slight_smile:

I have found that when I do a query using DRUID Json, the lookup works!

{
“queryType” : “topN”,
“dataSource” : “sms_detail”,
“intervals” : [“2019-01-30/2019-02-02”],
“granularity” : “all”,
“dimension”: {
“type”: “extraction”,
“dimension”: “carrierid”,
“outputName”: “name”,
“extractionFn”: {
“type”: “registeredLookup”,
“lookup”: “mysql_carriername”
}
},

“metric” : “count”,
“threshold” : 10,
“aggregations” : [
{
“type” : “count”,
“name” : “count”
}
]
}

``

but if do a query via sql (I’m guessing maybe the way I am using the LOOKUP function is incorrect?)

My sql:

select count(*),LOOKUP(‘carrierid’,‘mysql_carriername’) from sms_detail where __time < ‘2019-02-01 01:00:00’

``

again this yields an error:

Error: Error -1 (00000) : Error while executing SQL “select count(),LOOKUP(’’,‘mysql_carriername’) from sms_detail where __time < ‘2019-02-01 01:00:00’”: Remote driver error: JsonMappingException: No content to map due to end-of-input
at [Source: ; line: 1, column: 1]
SQLState: 00000
ErrorCode: -1

``

in the logs (broker we find):

2019-06-19T13:07:22,578 WARN [qtp272220100-136] org.eclipse.jetty.server.HttpChannel - /druid/v2/sql/avatica/
java.lang.AssertionError: Type mismatch:
rowtype of new rel:
RecordType(BIGINT NOT NULL EXPR$0, VARCHAR CHARACTER SET “UTF-16LE” COLLATE “UTF-16LE$en_US$primary” EXPR$1) NOT NULL
rowtype of set:
RecordType(BIGINT NOT NULL EXPR$0, VARCHAR CHARACTER SET “UTF-16LE” COLLATE “UTF-16LE$en_US$primary” NOT NULL EXPR$1) NOT NULL
at org.apache.calcite.util.Litmus$1.fail(Litmus.java:31) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.calcite.plan.RelOptUtil.equal(RelOptUtil.java:1857) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.calcite.plan.volcano.RelSubset.add(RelSubset.java:276) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.calcite.plan.volcano.RelSet.add(RelSet.java:148) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.calcite.plan.volcano.VolcanoPlanner.addRelToSet(VolcanoPlanner.java:1633) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.calcite.plan.volcano.VolcanoPlanner.registerImpl(VolcanoPlanner.java:1579) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.calcite.plan.volcano.VolcanoPlanner.register(VolcanoPlanner.java:859) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.calcite.plan.volcano.VolcanoPlanner.ensureRegistered(VolcanoPlanner.java:879) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.calcite.plan.volcano.VolcanoPlanner.ensureRegistered(VolcanoPlanner.java:1755) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.calcite.plan.volcano.VolcanoRuleCall.transformTo(VolcanoRuleCall.java:135) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:234) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.calcite.rel.rules.ReduceExpressionsRule$ProjectReduceExpressionsRule.onMatch(ReduceExpressionsRule.java:290) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:212) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:646) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.calcite.tools.Programs$RuleSetProgram.run(Programs.java:339) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:358) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.calcite.prepare.PlannerImpl.transform(PlannerImpl.java:337) ~[calcite-core-1.17.0-iap1.jar:1.17.0-iap1]
at org.apache.druid.sql.calcite.planner.DruidPlanner.planWithDruidConvention(DruidPlanner.java:122) ~[druid-sql-0.14.0-incubating-iap10.jar:0.14.0-incubating-iap10]
at org.apache.druid.sql.calcite.planner.DruidPlanner.plan(DruidPlanner.java:92) ~[druid-sql-0.14.0-incubating-iap10.jar:0.14.0-incubating-iap10]
at org.apache.druid.sql.SqlLifecycle.plan(SqlLifecycle.java:143) ~[druid-sql-0.14.0-incubating-iap10.jar:0.14.0-incubating-iap10]
at org.apache.druid.sql.SqlLifecycle.planAndAuthorize(SqlLifecycle.java:209) ~[druid-sql-0.14.0-incubating-iap10.jar:0.14.0-incubating-iap10]
at org.apache.druid.sql.avatica.DruidStatement.prepare(DruidStatement.java:156) ~[druid-sql-0.14.0-incubating-iap10.jar:0.14.0-incubating-iap10]
at org.apache.druid.sql.avatica.DruidMeta.prepareAndExecute(DruidMeta.java:191) ~[druid-sql-0.14.0-incubating-iap10.jar:0.14.0-incubating-iap10]
at org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:206) ~[avatica-core-1.10.0.jar:1.10.0]
at org.apache.calcite.avatica.remote.Service$PrepareAndExecuteRequest.accept(Service.java:928) ~[avatica-core-1.10.0.jar:1.10.0]
at org.apache.calcite.avatica.remote.Service$PrepareAndExecuteRequest.accept(Service.java:880) ~[avatica-core-1.10.0.jar:1.10.0]
at org.apache.calcite.avatica.remote.AbstractHandler.apply(AbstractHandler.java:94) ~[avatica-core-1.10.0.jar:1.10.0]
at org.apache.calcite.avatica.remote.JsonHandler.apply(JsonHandler.java:52) ~[avatica-core-1.10.0.jar:1.10.0]
at org.apache.calcite.avatica.server.AvaticaJsonHandler.handle(AvaticaJsonHandler.java:130) ~[avatica-server-1.10.0.jar:1.10.0]
at org.apache.druid.sql.avatica.DruidAvaticaHandler.handle(DruidAvaticaHandler.java:59) ~[druid-sql-0.14.0-incubating-iap10.jar:0.14.0-incubating-iap10]
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:61) ~[jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503]
at org.eclipse.jetty.server.handler.StatisticsHandler.handle(StatisticsHandler.java:169) ~[jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503]
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132) ~[jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503]
at org.eclipse.jetty.server.Server.handle(Server.java:531) ~[jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503]
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:352) [jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503]
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:260) [jetty-server-9.4.10.v20180503.jar:9.4.10.v20180503]
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:281) [jetty-io-9.4.10.v20180503.jar:9.4.10.v20180503]
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:102) [jetty-io-9.4.10.v20180503.jar:9.4.10.v20180503]
at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:118) [jetty-io-9.4.10.v20180503.jar:9.4.10.v20180503]
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:333) [jetty-util-9.4.10.v20180503.jar:9.4.10.v20180503]
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:310) [jetty-util-9.4.10.v20180503.jar:9.4.10.v20180503]
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:168) [jetty-util-9.4.10.v20180503.jar:9.4.10.v20180503]
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:126) [jetty-util-9.4.10.v20180503.jar:9.4.10.v20180503]
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:366) [jetty-util-9.4.10.v20180503.jar:9.4.10.v20180503]
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:760) [jetty-util-9.4.10.v20180503.jar:9.4.10.v20180503]
at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:678) [jetty-util-9.4.10.v20180503.jar:9.4.10.v20180503]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_212]

``

finally: (my lookup):

Enter c{
“__default”: {
“mysql_carriername”: {
“version”: “v0”,
“lookupExtractorFactory”: {
“type”: “cachedNamespace”,
“extractionNamespace”: {
“type”: “jdbc”,
“connectorConfig”: {
“createTables”: true,
“connectURI”: “jdbc:mysql://uswest2-dev-almisc-001.aws-dev:3306/meta”,
“user”: “admin”,
“password”: “admin”
},
“table”: “xyz_carrier”,
“keyColumn”: “carrierId”,
“valueColumn”: “name”,
“pollPeriod”:600000
},
“firstCacheTimeout”: 120000,
“injective”: false
}
}
}
}
ode here…

``

Looking at the sql docs:

```LOOKUP(expr, lookupName)- ``Look up expr in a registered query-time lookup table.`

so…am I doing something wrong what is the expression if not the dimension name…, or…

#10

And the look up definition is:

{
“__default”: {
“mysql_carriername”: {
“version”: “v0”,
“lookupExtractorFactory”: {
“type”: “cachedNamespace”,
“extractionNamespace”: {
“type”: “jdbc”,
“connectorConfig”: {
“createTables”: true,
“connectURI”: “jdbc:mysql://uswest2-dev-almisc-001.aws-dev:3306/meta”,
“user”: “admin”,
“password”: “admin”
},
“table”: “xyz_carrier”,
“keyColumn”: “carrierId”,
“valueColumn”: “name”,
“pollPeriod”:600000
},
“firstCacheTimeout”: 120000,
“injective”: false
}
}
}
}

``

#11

I see the keyColumn value in the lookup definition is carrierId whereas as the dimension is carrierid. Notice the capitali ‘I’. I think the keyColumn value should match the dimension value.

Thanks,

Sashi

#12

I tried as you suggested, and re-created the lookup:

{
“__default”: {
“mysql_carriername”: {
“version”: “v0”,
“lookupExtractorFactory”: {
“type”: “cachedNamespace”,
“extractionNamespace”: {
“type”: “jdbc”,
“connectorConfig”: {
“createTables”: true,
“connectURI”: “jdbc:mysql://uswest2-dev-almisc-001.aws-dev:3306/meta”,
“user”: “admin”,
“password”: “admin”
},
“table”: “xyz_carrier”,
“keyColumn”: “carrierid”,
“valueColumn”: “name”,
“pollPeriod”:600000
},
“firstCacheTimeout”: 120000,
“injective”: false
}
}
}
}

however with the same result :frowning:

again it works fine via druid/v2 queries, but fails via sql/calcite

#13

Also what doesn’t make sense:

The lookup definition:

we specify the table, and 2 columns (so I assume both of those are columns in the database, so at the least I would expect these value shown below match the ‘case’ of values in the Database.

“table”: “xyz_carrier”,
“keyColumn”: “carrierid”,
“valueColumn”: "name

Then when invoking the lookup function, we would specify the ‘dimension’ to be passed in as the input field…which in theory could be any dimension…obviously the dimension would need to ‘match’ with the value in the keyColumn to return something useful.

How can get more info out of the logs? I have tired enabling debug in:

_common/common.runtime.properties

setting:

druid.emitter=logging
druid.emitter.logging.logLevel=debug

and even changing: log4j2.xml ‘root level’ to debug

#14

Resolved :slight_smile:

INVALID SQL:

select LOOKUP('carrierid','mysql_carriername2') from sms_detail limit 5 ;

VALID SQL select LOOKUP(carrierid,'mysql_carriername2') from sms_detail limit 5 ;

It really would be nice to have an example or 2 added to the docs.

#15

I am glad that it got resolved for you.
Thanks to Sashi.

Yes, will make note of your recommendation to updated docs.

Thanks,
Mohan Vedicherla
Customer Success