Marc, you are mixing up two different pieces here. Firstly, however you
query drill, you will have to set up the connection from Drill to your file
system. It matters not whether you are using JDBC, the C-client or REST.. you
must have that working before any query will work.
In the configuration example you sent
"storage": {
cp: {
type: "file",
connection: "file:///",
enabled:true,
formats: {
"csv" : {
type: "text",
extensions: [ "json" ],
delimiter: ","
}
}
}
}
This is not correct. If you notice under "csv", you've configured Drill to
look for files ending in .JSON but read them as if they are a CSV file. This
will not work. (Unless you save your csv files with a .json extension)
First let's get that fixed. Then, once you can execute a query, then we can
show you how to query Drill programmatically. The code James sent you from the
docs is really all you need to query Drill from Java. You will need the Drill
JDBC driver in your class path, and then you should be able to cut/paste that
code into a class and get it to work.
I hope this helps.
Best,
-- C
> On Nov 27, 2022, at 12:41 PM, marc nicole <mk1853...@gmail.com> wrote:
>
> Thanks,
>
> I understand that, in order to use Drill from within Java code, it is
> necessary to go through JDBC (even if I am querying a local file system)
> right ? if yes, how to find examples of such Java codes ?
> Because using Shell it is not the case since I can query Json solely by
> setting a local workspace...
>
> Le dim. 27 nov. 2022 à 17:27, James Turton <dz...@apache.org> a écrit :
>
>> The first hop in the sequence Java App -> Drill -> JSON File is your
>> application communicating with Drill using the Drill JDBC driver and
>> standard JDBC API calls like createStatement() and execute() to send SQL
>> queries to Drill. Those queries will be run over JSON files if that's
>> what you've put in the FROM clause or, for other FROM clauses, they will
>> be run over external RDBMSes or CSV files or Kafka etc.
>>
>> It does not matter if you have no relational database, Drill presents a
>> relational view of files, e.g. JSON, via SQL.
>>
>>
>> https://drill.apache.org/docs/using-the-jdbc-driver/#example-of-connecting-to-drill-programmatically
>>
>> On 2022/11/27 11:32, marc nicole wrote:
>>> *"Drill only runs queries written in SQL."*
>>>
>>> I know and i want to use an SQL written query and apply it on a JSON file
>>> all using Java syntax.
>>>
>>> *" You can send that SQL from your Java application to Drill using JDBC
>> or
>>> Drill's REST API"*
>>>
>>> How to do this using Java? what is the required code ? executeQuery on a
>>> Statement using a Connection won't work since i don't use a database to
>> go
>>> in the direction of JDBC but instead use a simple JSON file
>>>
>>> Le sam. 26 nov. 2022 à 10:51, marc nicole <mk1853...@gmail.com> a écrit
>> :
>>>
>>>> Hi,
>>>> Thanks,
>>>>
>>>> *"Drill only runs queries written in SQL."*
>>>>
>>>> I know and i want to use an SQL written query and apply it on a JSON
>> file
>>>> all using Java syntax.
>>>>
>>>> *" You can send that SQL from your Java application to Drill using JDBC
>> or
>>>> Drill's REST API"*
>>>>
>>>> How to do this using Java? what is the required code ? executeQuery on a
>>>> Statement using a Connection won't work since i don't use a database to
>> go
>>>> in the direction of JDBC but instead use a simple JSON file
>>>>
>>>> Le ven. 25 nov. 2022 à 09:25, James Turton <dz...@apache.org> a écrit :
>>>>
>>>>> my data files could get big. Is Drill Spark integration a solution in
>> that
>>>>> case?
>>>>>
>>>>> Drill remains a solution if your data gets big because it scales
>>>>> horizontally like Spark. You will have to replace the Windows Desktop
>>>>> folder with some scalable, network enabled storage, however,
>> irrespective
>>>>> of which query engine you choose. Neither Drill nor Spark provide a
>> storage
>>>>> layer themselves but compatible options include HDFS and S3.
>>>>>
>>>>> After setting the workspace to query the file system, how to execute
>> such
>>>>> query in Java syntax?
>>>>>
>>>>> Drill only runs queries written in SQL. You can send that SQL from your
>>>>> Java application to Drill using JDBC or Drill's REST API. If you
>> prefer to
>>>>> generate the SQL from object oriented Java expressions, take a look at
>>>>> jOOQ <https://www.jooq.org/>. There might be a little dialect work
>>>>> required to make jOOQ fully compatible with Drill but (a) we'd be
>> prepared
>>>>> to help you with that and (b) Drill's SQL dialect is by and large
>> vanilla
>>>>> ANSI SQL:2003.
>>>>>
>>>>> Regards
>>>>> James
>>>>>
>>>>> On 2022/11/25 09:54, marc nicole wrote:
>>>>>
>>>>> Hi,
>>>>>
>>>>> After setting the workspace to query the file system, how to execute
>> such
>>>>> query in Java syntax?
>>>>>
>>>>> Le ven. 25 nov. 2022 à 02:25, Charles Givre <cgi...@gmail.com> <
>> cgi...@gmail.com> a écrit :
>>>>>
>>>>>
>>>>> Hi Marc,
>>>>> I should have asked, are you running Drill on a single windows machine?
>>>>> If so, Drill will be able to query anything you throw at it. If your
>> data
>>>>> starts to get bigger than a single machine can handle, you'll need to
>> set
>>>>> up a Drill cluster with multiple nodes. This is no different than
>> Spark. I
>>>>> would suggest using Drill to convert the data to parquet format.
>> Often you
>>>>> can achieve a 10x reduction in file size and extreme improvements in
>> query
>>>>> speed.
>>>>>
>>>>> As for configuring Drill, take a look here:
>> https://drill.apache.org/docs/workspaces/. This explains how to set up
>>>>> a workspace. What you'll want to do is set the workspace to the path to
>>>>> your desktop. Then you can query the files as noted below.
>>>>> Best,
>>>>> -- C
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Nov 24, 2022, at 6:05 PM, marc nicole <mk1853...@gmail.com> <
>> mk1853...@gmail.com> wrote:
>>>>>
>>>>> also how to execute such queries as SELECT *
>>>>> FROM dfs.desktop.`file.json` in Java ?
>>>>>
>>>>> Le jeu. 24 nov. 2022 à 23:31, Charles Givre <cgi...@gmail.com> <
>> cgi...@gmail.com> a écrit :
>>>>>
>>>>>
>>>>> Hi Marc,
>>>>> Welcome to Drill! Firstly, take a look at the docs for querying a file
>>>>> system:
>>>>>
>>>>> https://drill.apache.org/docs/querying-a-file-system-introduction/
>>>>>
>>>>> When you start up drill out of the box, there is a connector called dfs
>>>>> which points to the local filesystem. You can configure a workspace to
>>>>> your desktop folder, then all you have to do is write a query like:
>>>>>
>>>>> SELECT *
>>>>> FROM dfs.desktop.`file.json`
>>>>>
>>>>> If you're looking to do this programmatically from Java and your data
>>>>> isn't too big, the easiest way is probably to use Drill's REST API (
>> https://drill.apache.org/docs/rest-api-introduction/). You can make a
>>>>> simple HtTP call to Drill and get the data that way.
>>>>>
>>>>> Hope this helps!
>>>>> -- C
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Nov 24, 2022, at 5:02 PM, marc nicole <mk1853...@gmail.com> <
>> mk1853...@gmail.com> wrote:
>>>>>
>>>>> Hi,
>>>>>
>>>>> I want to query a JSON file placed in Desktop folder (Windows).
>>>>> How to do that in Java ?
>>>>>
>>>>> PS: i saw this type of code :
>>>>>
>>>>> Connection con = null;
>>>>>
>>>>> con = new Driver().connect(DRILL_JDBC_LOCAL_URI,
>>>>>
>>>>> getDefaultProperties());
>>>>>
>>>>> Statement stmt = con.createStatement();
>>>>> ResultSet rs = stmt.executeQuery(DRILL_SAMPLE_QUERY);...
>>>>>
>>>>>
>>>>> But that requires using JDBC and to place JSON in jar file within CP of
>>>>> Drill which i don't want;
>>>>>
>>>>> Thanks.
>>>>>
>>>>>
>>>>>
>>>
>>
