error 2886 could not open file ; No such file or Directory

Ashutosh_Dubey

I am trying to create Flex external tables for the json file residing in the Maprfs. When I try to create the view using the following command

select compute_flextable_keys_and_build_view('active');             // 'active' is the external table                                                                                                                     created
I get the following error message

ERROR 2886:  Could not open file [TestFreaks/SocialAnalytics/TestFreaks/ActiveHealthSample.json] for reading; No such file or directory
HINT:  Make sure the file is available on the specified node.  If using ON ANY NODE, it must be available at this path on all nodes in the cluster since any node could load the file     

Everything looks fine and data is present on all the nodes in the specified path. I tried to create flex table and its working fine, but when I try external flex table then the data is unable to load and form a view for querying.

Please help in suggesting some pointers to the solution.

wwong2

Hi, I tried it here locally where json file was on local machine where Vertica is and did not get your error message.
1) How did you define your flex external table?
2) Can you try where json file is locally based instead of on the maprfs to try to isolate issue
3) what version are you on?

Regards
 

Ashutosh_Dubey

Hi Wayne,

1) How did you define flex external table ?
-> create flex external table active() as copy from 'TestFreaks/SocialAnalytics/TestFreaks/ActiveHealthSample.json' parser fjsonparser();

2) Can you try where json file is locally based instead of on the maprfs to try to isolate issue?
-> I tried initially with this as well but getting the same error. I tried creating flex table instead of external table flex, and that worked. flex table was able to load data from the NFS mount point as well, but external table is my concern which is not able to load the data.

3) what version are you on?
-> vertica-7.0.2-1.x86_64

wwong2

Hi, I see the issue.  Was able to recreate and fix it.  It looks like you are missing a / in your path.
Here is my example

p=> create flex external table active() as copy from 'home/dbadmin/data/mountains.json' parser fjsonparser();
CREATE TABLE
p=> select compute_flextable_keys_and_build_view ('active');
ERROR 2886:  Could not open file [home/dbadmin/data/mountains.json] for reading; No such file or directory
HINT:  Make sure the file is available on the specified node.  If using ON ANY NODE, it must be available at this path on all nodes in the cluster since any node could load the file
p=> create flex external table active1() as copy from '/home/dbadmin/data/mountains.json' parser fjsonparser();
CREATE TABLE
p=> select compute_flextable_keys_and_build_view ('active1');
                               compute_flextable_keys_and_build_view                                
----------------------------------------------------------------------------------------------------
 Please see public.active1_keys for updated keys
The view public.active1_view is ready for querying
(1 row)

The issue I see is you do not have the / to begin with when defining the table.

Try that.

Regards
 

Ashutosh_Dubey

Hi Wayne
That worked thanx. What a basic mistake.
Regards