>The problems I see are when the matching files are not in the same format (number of columns) vs datatype in the rows (headers which are strings should be excluded)
thanks, it works for me now On Wed, Oct 19, 2016 at 10:14 AM, Lee, David <[email protected]> wrote: > It does.. The problems I see are when the matching files are not in the > same format (number of columns) vs datatype in the rows (headers which are > strings should be excluded) > > Sometimes you have to use a table alias as well when querying JSON files.. > > Both of the following work for me.. > > select * from dfs.`/proj/EquityCalc/common/rawsignals/esg_*/r20160919.csv` > where columns[0] = 'Z91QP8V07'; > > select substr(filename,2,8) as as_of, * from table(dfs.`/proj/EquityCalc/ > common/rawsignals/esg_ia_score/r201609??.csv`(type => 'text', > fieldDelimiter => ',', extractHeader => true)) where invariant_id = > 'Z91QP8V07'; > > David Lee > Vice President | BlackRock > Phone: +1.415.670.2744 | Mobile: +1.415.706.6874 > > -----Original Message----- > From: Anton Kravchenko [mailto:[email protected]] > Sent: Wednesday, October 19, 2016 9:51 AM > To: [email protected] > Subject: Drill wildcard support to match files in directory > > Hi there, > > From https://urldefense.proofpoint.com/v2/url?u=https-3A__drill. > apache.org_docs_text-2Dfiles-2Dcsv-2Dtsv-2Dpsv_&d=DQIBaQ&c= > zUO0BtkCe66yJvAZ4cAvZg&r=SpeiLeBTifecUrj1SErsTRw4nAqzMxT043sp_gndNeI&m= > lt0SAPBmxPz1803wY-xThMa_7KMpUj1lQtDgVVU0axw&s=HLB-- > Hp0jVjmk3Ov2hnTMwo0WJVNEOX5c0KoF5u0j2I&e= example: > > "SELECT columns[0] as `DATE`, > columns[1] as `AIRLINE`, > CAST(columns[11] AS DOUBLE) as `PASSENGER_COUNT` FROM > dfs.`/opendata/Passenger/SFO_Passenger_Data/*.csv` > WHERE CAST(columns[11] AS DOUBLE) < 5;" > > it looks like Apache Drill should be able to support wildcard to match > files in directory, is that right? > > P.S. > my experience: > 0: jdbc:drill:zk=local> select count (*) from dfs.tmp.`bar.csv`; > +----------+ > | EXPR$0 | > +----------+ > | 1000000 | > +----------+ > 1 row selected (2.93 seconds) > 0: jdbc:drill:zk=local> select count (*) from dfs.tmp.`ba*.csv`; > Error: VALIDATION ERROR: String index out of range: -1 > > SQL Query null > > Thank you, > Anton > > > This message may contain information that is confidential or privileged. > If you are not the intended recipient, please advise the sender immediately > and delete this message. See http://www.blackrock.com/ > corporate/en-us/compliance/email-disclaimers for further information. > Please refer to http://www.blackrock.com/corporate/en-us/compliance/ > privacy-policy for more information about BlackRock’s Privacy Policy. > > For a list of BlackRock's office addresses worldwide, see > http://www.blackrock.com/corporate/en-us/about-us/contacts-locations. > > © 2016 BlackRock, Inc. All rights reserved. >
