CREATE EXTERNAL TABLE tablename (column_spec) LOCATION ( 'gphdfs://location') FORMAT 'AVRO' 

The location can be an individual Avro file or a directory containing a set of Avro files. If the location specifies multiple files (a directory name or a file name containing wildcard characters), Greenplum Database uses the schema in the first file of the directory as the schema of the whole directory. For the file name you can specify the wildcard character * to match any number of characters.

You can add parameters after the file specified in the location. You add parameters with the http query string syntax that starts with ? and & between field and value pairs.

For readable external tables, the only valid parameter is schema. The gphdfs uses this schema instead of the Avro file schema when reading Avro files. See Avro Schema Overrides for Readable External Tables.

For writable external tables, you can specify schema, namespace, and parameters for compression.

 'compress=true&codec=snappy'

These two sets of parameters specify deflate compression and are equivalent:

 'compress=true&codec=deflate&codec_level=1'
 'compress=true&codec_level=1'

This table lists the Avro primitive data types and the Greenplum Database type it is converted to.

This table lists the Avro complex data types and the and the Greenplum Database type it is converted to.

When you specify schema for a readable external table that specifies an Avro file as a source, Greenplum Database uses the schema when reading data from the Avro file. The specified schema overrides the Avro file schema.

You can specify a file that contains an Avro schema as part of the location paramter CREATE EXTERNAL TABLE command, to override the Avro file schema. If a set of Avro files contain different, related schemas, you can specify an Avro schema to retrieve the data common to all the files.

Greenplum Database extracts the data from the Avro files based on the field name. If an Avro file contains a field with same name, Greenplum Database reads the data , otherwise a NULL is returned.

For example, if a set of Avro files contain one of the two different schemas. This is the original schema.

{
	"type":"record",
	"name":"tav2",
	"namespace":"public.avro",
	"doc":"",
	"fields":[
		{"name":"id","type":["null","int"],"doc":""},
		{"name":"name","type":["null","string"],"doc":""},
		{"name":"age","type":["null","long"],"doc":""},
		{"name":"birth","type":["null","string"],"doc":""}
	]
}

This updated schema contains a comment field.

{
	"type":"record",
	"name":"tav2",
	"namespace":"public.avro",
	"doc":"",
	"fields":[
		{"name":"id","type":["null","int"],"doc":""},
		{"name":"name","type":["null","string"],"doc":""},
		{"name":"birth","type":["null","string"],"doc":""},
		{"name":"age","type":["null","long"],"doc":""},
		{"name":"comment","type":["null","string"],"doc":""}
	]
}

{
	"type":"record",
	"name":"tav2",
	"namespace":"public.avro",
	"doc":"",
	"fields":[
		{"name":"id","type":["null","int"],"doc":""},
		{"name":"name","type":["null","string"],"doc":""},
		{"name":"birth","type":["null","string"],"doc":""},
		{"name":"comment","type":["null","string"],"doc":""}
	]
}

CREATE WRITABLE EXTERNAL TABLE cust_avro(id int, name text, birth date) 
   LOCATION ('gphdfs://my_hdfs:8020/tmp/cust*.avro
      ?schema=hdfs://my_hdfs:8020/avro/cust.avsc')
   FORMAT 'avro';

When reading the Avro data, if Greenplum Database reads a file that does not contain a comment field, a NULL is returned for the comment data.

When you create a writable external table to write data to an Avro file, each table row is an Avro record and each table column is an Avro field. When writing an Avro file, the default compression algorithm is deflate.

For a writable external table, if the schema option is not specified, Greenplum Database creates an Avro schema for the Avro file based on the Greenplum Database external table definition. The name of the table column is the Avro field name. The data type is a union data type. See the following table:

You can specify a schema with the schema option. When you specify a schema, the file can be on the segment hosts or a file on the HDFS that is accessible to Greenplum Database. For a local file, the file must exist in all segment hosts in the same location. For a file on the HDFS, the file must exist in the same cluster as the data file.

This example schema option specifies a schema on an HDFS.

'schema=hdfs://mytest:8000/avro/array_simple.avsc'

This example schema option specifies a schema on the host file system.

'schema=file:///mydata/avro_schema/array_simple.avsc'

For a Greenplum Database writeable external table definition, columns cannot specify the NOT NULL clause.

Greenplum Database does not support the Avro map data type and returns an error when encountered.

When Greenplum Database reads an array from an Avro file, the array is converted to the literal text value. For example, the array [1,3] is converted to '{1,3}'.

User defined types (UDT), including array UDT, are supported. For a writable external table, the type is converted to string.

 CREATE EXTERNAL TABLE avro1 (id int, ba bytea[]) 
   LOCATION ('gphdfs://my_hdfs:8020/avro/singleAvro/array2.avro') 
   FORMAT 'avro';

CREATE WRITABLE EXTERNAL TABLE at1w(id int, names text[], nums int[]) 
   LOCATION ('gphdfs://my_hdfs:8020/tmp/at1
      ?schema=hdfs://my_hdfs:8020/avro/array_simple.avsc')
   FORMAT 'avro';

CREATE WRITEABLE EXTERNAL TABLE command that writes to an Avro file and specifies a namespace for the Avro schema.

CREATE WRITABLE EXTERNAL TABLE atudt1 (id int, info myt, birth date, salary numeric ) 
   LOCATION ('gphdfs://my_hdfs:8020/tmp/emp01.avro
      ?namespace=public.pivotal.avro') 
   FORMAT 'avro';