avro external table

Hi Curtis,

Thanks for sending me the Avro file.

If I copy it to a local file system, and use Vertica 9.2, it works okay...

Example:

dbadmin=> create flex table avro_sample() ;
CREATE TABLE

dbadmin=> copy avro_sample from '/home/dbadmin/twitter.avro' parser favroparser();
 Rows Loaded
-------------
           2
(1 row)

dbadmin=> SELECT compute_flextable_keys_and_build_view ('avro_sample');
                                   compute_flextable_keys_and_build_view
------------------------------------------------------------------------------------------------------------
 Please see public.avro_sample_keys for updated keys
The view public.avro_sample_view is ready for querying
(1 row)

dbadmin=> SELECT * FROM public.avro_sample_view;
    __name__    | timestamp  |                tweet                |  username
----------------+------------+-------------------------------------+------------
 twitter_schema | 1366150681 | Rock: Nerf paper, scissors is fine. | miguno
 twitter_schema | 1366154481 | Works as intended.  Terran is IMBA. | BlizzardCS
(2 rows)

dbadmin=> create flex external table avro_test() as copy from '/home/dbadmin/twitter.avro' parser favroparser();
CREATE TABLE

dbadmin=> SELECT compute_flextable_keys_and_build_view ('avro_test') ;
                                 compute_flextable_keys_and_build_view
--------------------------------------------------------------------------------------------------------
 Please see public.avro_test_keys for updated keys
The view public.avro_test_view is ready for querying
(1 row)

dbadmin=> SELECT * FROM public.avro_test_view;
    __name__    | timestamp  |                tweet                |  username
----------------+------------+-------------------------------------+------------
 twitter_schema | 1366150681 | Rock: Nerf paper, scissors is fine. | miguno
 twitter_schema | 1366154481 | Works as intended.  Terran is IMBA. | BlizzardCS
(2 rows)

Yea, it seems to be only an issue when it resides on s3. Not sure why. My guess is it's a bug.

Seems okay from my bucket...

dbadmin=> drop table avro_test;
DROP TABLE

dbadmin=> create flex external table avro_test() as copy from 's3://knicely-vertica/twitter.avro' parser favroparser() ;
CREATE TABLE

dbadmin=> SELECT compute_flextable_keys_and_build_view ('avro_test') ;
                                 compute_flextable_keys_and_build_view
--------------------------------------------------------------------------------------------------------
 Please see public.avro_test_keys for updated keys
The view public.avro_test_view is ready for querying
(1 row)

dbadmin=> SELECT * FROM public.avro_test_view;
    __name__    | timestamp  |                tweet                |  username
----------------+------------+-------------------------------------+------------
 twitter_schema | 1366150681 | Rock: Nerf paper, scissors is fine. | miguno
 twitter_schema | 1366154481 | Works as intended.  Terran is IMBA. | BlizzardCS
(2 rows)

Ok, Jim and I figured this out (mostly Jim). Seems if you don't have the AWSAuth value set correctly, you get this error (this horrible, worthless error). But oddly, you can LOAD the file correctly, just not compute the keys on it. Weird. This can be solved by either setting the permissions on the file itself (Setting the permissions on the bucket don't seem to do it) - to public READ access. Again, setting the permissions on the bucket don't seem to work. It has to be set on the file. Or, setting the AWSAuth will fix it.

I have created JIRA ver-65602 for this.

Teamwork