CSV Support in AsterixDB

Output Object Type

Background: The result of any AQL query is an unordered list of instances, where each instance is an instance of an AQL datatype. When requesting CSV output, there are some restrictions on the legal datatypes in this unordered list due to the limited expressability of CSV:

1. Each instance must be of a object type.
2. Each instance must be of the same object type.
3. The object type must conform to the content and type restrictions mentioned in the introduction.

While it would be possible to structure your query to cast all result instances to a given type, it is not necessary. AQL offers a built-in feature which will automatically cast all top-level instances in the result to a specified named ADM object type. To enable this feature, use a set statement prior to the query to set the parameter output-record-type to the name of an ADM type. This type must have already been defined in the current dataverse.

For example, the following request will ensure that all result instances are cast to the csv_type type declared earlier:

use dataverse csv;
set output-record-type "csv_type";

for $n in dataset "csv_set" return $n;

In this case the casting is redundant since by definition every value in csv_set is already of type csv_type. But consider a more complex query where the result values are created by joining fields from different underlying datasets, etc.

Two notes about output-record-type:

1. This feature is not strictly related to CSV; it may be used with any output formats (in which case, any object datatype may be specified, not subject to the limitations specified in the introduction of this page).
2. When the CSV output format is requested, output-record-type is in fact required, not optional. This is because the type is used to determine the field names for the CSV header and to ensure that the ordering of fields in the output is consistent (which is obviously vital for the CSV to make any sense).

Request the CSV Output Format

When sending requests to the Asterix HTTP API, Asterix decides what format to use for rendering the results in one of two ways:

• A HTTP query parameter named “output”, which must be set to one of the following values: JSON, CSV, or ADM.

• Based on the Accept HTTP header

By default, Asterix will produce JSON output. To select CSV output, pass the parameter output=CSV, or set the Accept header on your request to the MIME type text/csv. The details of how to accomplish this will of course depend on what tools you are using to contact the HTTP API. Here is an example from a Unix shell prompt using the command-line utility “curl” and specifying the "output query parameter:

curl -G "http://localhost:19002/query" \
    --data-urlencode 'output=CSV' \
    --data-urlencode 'query=use dataverse csv;
          set output-record-type "csv_type";
          for $n in dataset csv_set return $n;'

Alternately, the same query using the Accept header:

curl -G -H "Accept: text/csv" "http://localhost:19002/query" \
    --data-urlencode 'query=use dataverse csv;
          set output-record-type "csv_type";
          for $n in dataset csv_set return $n;'

Similarly, a trivial Java program to execute the above sample query and selecting CSV output via the Accept header would be:

import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;

public class AsterixExample {
    public static void main(String[] args) throws Exception {
        String query = "use dataverse csv; " +
            "set output-record-type \"csv_type\";" +
            "for $n in dataset csv_set return $n";
        URL asterix = new URL("http://localhost:19002/query?query=" +
                              URLEncoder.encode(query, "UTF-8"));
        HttpURLConnection conn = (HttpURLConnection) asterix.openConnection();
        conn.setRequestProperty("Accept", "text/csv");
        BufferedReader result = new BufferedReader
            (new InputStreamReader(conn.getInputStream()));
        String line;
        while ((line = result.readLine()) != null) {
            System.out.println(line);
        }
        result.close();
    }
}

For either of the above examples, the output would be:

1,18.5,"Peter Krabnitz"
2,74.5,"Jesse Stevens"

assuming you had already run the previous examples to create the dataverse and populate the dataset.

Outputting CSV with a Header

By default, AsterixDB will produce CSV results with no header line. If you want a header, you may explicitly request it in one of two ways:

• By passing the HTTP query parameter “header” with the value “present”

• By specifying the MIME type {{text/csv; header=present}} in your HTTP Accept: header. This is consistent with RFC 4180.

Issues with open datatypes and optional fields

As mentioned earlier, CSV is a rigid format. It cannot express objects with different numbers of fields, which ADM allows through both open datatypes and optional fields.

If your output object type contains optional fields, this will not result in any errors. If the output data of a query does not contain values for an optional field, this will be represented in CSV as null.

If your output object type is open, this will also not result in any errors. If the output data of a query contains any open fields, the corresponding rows in the resulting CSV will contain more comma-separated values than the others. On each such row, the data from the closed fields in the type will be output first in the normal order, followed by the data from the open fields in an arbitrary order.

According to RFC 4180 this is not strictly valid CSV (Section 2, rule 4, “Each line should contain the same number of fields throughout the file”). Hence it will likely not be handled consistently by all CSV processors. Some may throw a parsing error. If you attempt to load this data into AsterixDB later using load dataset, the extra fields will be silently ignored. For this reason it is recommended that you use only closed datatypes as output object types. AsterixDB allows to use an open object type only to support cases where the type already exists for other parts of your application.