WHERE statement in google cloud datalab failing when using a list.

I'm using google cloud datalab and I'm trying to select data from a table based on the match in a list.

First I use a python cell to define my list

import gcp.bigquery as bq
samples = ['TCGA-CH-5751-01A', 'TCGA-EJ-5496-01A']

Then I create a cell with my sql query

%%sql --module test
SELECT 
    ParticipantBarcode, 
    SampleBarcode, 
FROM 
    [isb-cgc:tcga_201510_alpha.mRNA_UNC_HiSeq_RSEM]
WHERE SampleBarcode IN $samples
LIMIT 100

Then I would call this using another python cell

results = bq.Query(test, samples=samples).results().to_dataframe()

This fails because the WHERE statement is incorrect.

invalidQuery: Encountered " "IN" "IN ""

If I hardcode the names I want to match again in to the sql statment it works.

%%sql --module test2
SELECT 
    ParticipantBarcode, 
    SampleBarcode, 
FROM 
    [isb-cgc:tcga_201510_alpha.mRNA_UNC_HiSeq_RSEM]
WHERE SampleBarcode IN  ('TCGA-CH-5751-01A', 'TCGA-EJ-5496-01A')
LIMIT 100

I think this is because of how I'm passing in my list to sql, but I'm not sure how to properly do this on cloud data lab. Most of the python results I found when searching use python to craft the whole sql command, and I just want to add in the list.

Thanks.

Answers


This feature was added in the release on Feb 8 2016.


Need Your Help

Is it safe to store passwords and other sensitive info in cache files?

php security caching

I have a cache library that stores cached data in normal php files, with the data stored in arrays.

hbase refusing connection for remote user

java java-ee hadoop hbase

Hi I am new to hbase and hadoop.I have properly setup them but check them from same machine and it is working fine.Now when tried to connect from a java client remotly , this exception is coming.I