I don't believe the current reader can take a text field in a JSON object and
read it as TSV/CSV to get the columns. AS Neeraja mentioned, it will help you
can provide more specific information and perhaps a sample of what you are
trying to achieve. Are the text field with tab delimited fields exactly the
same in each JSON object, etc?
A couple of quick thoughts based on the limited information.
- Look at creating a UDF that allows to extract the tab delimited data in
columns from the text field.
- If the tab data is not very wide you can use existing SQL string functions to
extract the data in columns and cast as needed.
eg. a quick SQL hack to decompose an IP address (can get expense and very
cumbersome if you have a lot of fields in the TSV fields)
select p2.ip_address, p2.part1, p2.part2, substr(p2.rest2, 1,
locate('.',p2.rest2)-1) as part3,
substr(rest2, locate('.',rest2)+1) as part4
from
(select p1.ip_address, p1.part1, substr(rest1, 1, locate('.',rest1)-1) as part2,
substr(rest1, locate('.',rest1)+1) as rest2
from
(select ip_address, substr(ip_address, 1, locate('.',ip_address)-1) as part1,
substr(ip_address, locate('.',ip_address)+1) as rest1 from `/ip`) as p1) as p2
+---------------+--------+--------+--------+--------+
| ip_address | part1 | part2 | part3 | part4 |
+---------------+--------+--------+--------+--------+
| 172.16.254.1 | 172 | 16 | 254 | 1 |
+---------------+--------+--------+--------+--------+
- Write the tab delimited data out with a key to a CSV/TSV file with Drill and
see if you can join it back to the either JSON data.
If writing out the data is not an option, and you have a lot of TSV fields the
UDF option is likely your best bet, unless there is an option in the reader I'm
missing.
--Andries
> On Feb 24, 2016, at 12:41 PM, Neeraja Rentachintala
> <[email protected]> wrote:
>
> Jorge
> can you give an example of what you are looking to accomplish here.
> Based on your description, it seems to me that you might be able to use the
> functions listed here.
> https://drill.apache.org/docs/supported-data-types/#data-types-for-convert_to-and-convert_from-functions
>
>
>
> On Wed, Feb 24, 2016 at 12:14 PM, [email protected] <
> [email protected]> wrote:
>
>> Can you please reply to my question below? We need to know if it is
>> possible.. The company I work for is probably MapR's largest customer and I
>> would appreciate your help.
>>
>> Thanks,
>> Jorge
>>
>>
>> Sent from my iPhone
>>
>>> On Feb 18, 2016, at 9:48 PM, jorge gonzalez <[email protected]> wrote:
>>>
>>> Hello,
>>>
>>> The company I currently work for stores it's data in the form of java
>> objects in several MapR clusters. These java objects have a string field
>> with tab delimited data. They are looking to start using Apache Drill to
>> first load the java objects and then read the tab delimited data/string
>> field.
>>>
>>> Is this too difficult to accomplish? What are the necessary steps?
>>>
>>> Thanks in advance for your help.
>>>
>>> Regards,
>>> Jorge
>>
