2

New programmer with SQL and Apache Drill here. I'm trying to take this SQL command from DB1: 

SELECT screen_name, job_id, count(*) as counter
from twitter.mention t
WHERE t.job_id = 290
or t.job_id = 261
or t.job_id = 303
group by screen_name, job_id
order by counter desc
limit 60;

I'm trying to take this block and run it through Apache Drill, which I'm very new to. Of note, I'm using two databases, but only one variable is changing: screen_name (changing to from_user_name). It works fine in SQL, but it does not work in drill. Here is the drill code: 

statement = """
SELECT from_user_name, job_id, count(*) as counter
from twitter.tweet t
WHERE t.job_id = 290
or t.job_id = 261
or t.job_id = 303
group by from_user_name, job_id
order by counter desc
limit 60;"""

drill = PyDrill(host='host_name', port=8047)

if not drill.is_active():
    raise ImproperlyConfigured('Please run Drill first')


rows = drill.query(statement, timeout = 120)

df = rows.to_dataframe()
df.head(20)

Here is the error message: 

TransportError: TransportError(500, '{\n  "errorMessage" : "PARSE 
ERROR: Encountered \\";\\" at line 9, column 9.\\nWas expecting one 
of:\\n    <EOF> \\n    \\"OFFSET\\" ...\\n    \\"FETCH\\" ...\\n    
\\n\\nSQL Query \\nSELECT from_user_name, job_id, count(*) as 
counter\\nfrom twitter.tweet t\\nWHERE t.job_id = 290\\nor t.job_id = 
261\\nor t.job_id = 303\\ngroup by from_user_name, job_id\\norder by 
counter desc\\nlimit 60;\\n        ^\\n\\n\\n[Error Id: 78df6d24-686b-
496f-8795-9b3d21d75740 on c04.h-spark.cgi.missouri.edu:31010]"\n}')
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Ryan
  • 71
  • 2
  • 7
  • What is your "original" database and which database is running under Apache Drill? – Tim Biegeleisen Dec 15 '17 at 01:47
  • the original database is twitter.mention and the new database under Apache Drill is twitter.tweet. It's for a project and our professor instructed us to use the tweet table. – Ryan Dec 15 '17 at 01:50
  • Also, thanks for the quick response! – Ryan Dec 15 '17 at 01:50
  • By "database," I mean something like MySQL, SQL Server, Oracle, Postgres, DB2, etc. Which one are you using in both places? – Tim Biegeleisen Dec 15 '17 at 01:55
  • Postgres. Sorry. I've been at this since 2pm CST. – Ryan Dec 15 '17 at 01:58
  • 1
    The error message seems to be saying that Drill doesn't like the semicolon in the query. As it would probably be ignored anyway, can you try _removing_ that semicolon after the `LIMIT` clause and then run again? – Tim Biegeleisen Dec 15 '17 at 02:05
  • Thanks for the tip. I did that and now it spits out an error that it cannot find twitter.tweet: TransportError: TransportError(500, '{\n "errorMessage" : "VALIDATION ERROR: From line 3, column 6 to line 3, column 12: Table \'twitter.tweet\' not found}') – Ryan Dec 15 '17 at 02:08
  • This is as far I can go, because I don't know Drill. I would suggest paring down your query to something very simple, and trying to get that to run. E.g. try `SELECT * FROM twitter.tweet` ... if even this won't run, you have some other code problems, and it isn't the SQL. – Tim Biegeleisen Dec 15 '17 at 02:11
  • Thanks so much for all of your help. I'll start with a simple query and build it out. – Ryan Dec 15 '17 at 02:15

1 Answers1

0

This depends strongly on your mapping in the Storage Plugins section. How did you name the storage components? E.g. if you connected to a MySQL database, the storage plugin might be called mysql.

Now, if you want to access data from a specific table, you first need to tell Drill the storage plugin. How should Drill otherwise know in which connection this table resides?

So for example with a MySQL connection called mysql, a database "mydb", a table "twitterfeed" and a column "message" the SELECT would look like this:

SELECT * FROM mysql.mydb.twitterfeed.message LIMIT 10;

This could easily be tested from within the Drill web UI which offers a very simple Query dialog.

Other data sources can be joined in this manner, e.g. with a table in a MongoDB:

SELECT * FROM mysql.mydb.twitterfeed.message 
JOIN mongodb.thedb.tablename ON mysql.mydb.twitterfeed.id = mongodb.thedb.tablename.externalid
LIMIT 10;

I did use SELECT * only for example purposes. Obviously this should always hold the correct column names and no asterik. If you are only using one database you could also use USE.

tobi6
  • 8,033
  • 6
  • 26
  • 41