blob: e2817f02ad3592a1449c84d305243a9dc0964599 [file] [log] [blame]
Ian Maxonbf2c56b2017-01-24 14:14:49 -08001<!DOCTYPE html>
2<!--
Ian Maxond5b11d82017-01-25 10:48:05 -08003 | Generated by Apache Maven Doxia at 2017-01-25
Ian Maxonbf2c56b2017-01-24 14:14:49 -08004 | Rendered using Apache Maven Fluido Skin 1.3.0
5-->
6<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
7 <head>
8 <meta charset="UTF-8" />
9 <meta name="viewport" content="width=device-width, initial-scale=1.0" />
Ian Maxond5b11d82017-01-25 10:48:05 -080010 <meta name="Date-Revision-yyyymmdd" content="20170125" />
Ian Maxonbf2c56b2017-01-24 14:14:49 -080011 <meta http-equiv="Content-Language" content="en" />
12 <title>AsterixDB &#x2013; CSV Support in AsterixDB</title>
13 <link rel="stylesheet" href="./css/apache-maven-fluido-1.3.0.min.css" />
14 <link rel="stylesheet" href="./css/site.css" />
15 <link rel="stylesheet" href="./css/print.css" media="print" />
16
17
18 <script type="text/javascript" src="./js/apache-maven-fluido-1.3.0.min.js"></script>
19
20
21
Ian Maxonbf2c56b2017-01-24 14:14:49 -080022
Ian Maxonbf2c56b2017-01-24 14:14:49 -080023
24 </head>
25 <body class="topBarDisabled">
26
27
28
29
30 <div class="container-fluid">
31 <div id="banner">
32 <div class="pull-left">
33 <a href="./" id="bannerLeft">
34 <img src="images/asterixlogo.png" alt="AsterixDB"/>
35 </a>
36 </div>
37 <div class="pull-right"> </div>
38 <div class="clear"><hr/></div>
39 </div>
40
41 <div id="breadcrumbs">
42 <ul class="breadcrumb">
43
44
Ian Maxond5b11d82017-01-25 10:48:05 -080045 <li id="publishDate">Last Published: 2017-01-25</li>
Ian Maxonbf2c56b2017-01-24 14:14:49 -080046
47
48
49 <li id="projectVersion" class="pull-right">Version: 0.9.0</li>
50
51 <li class="divider pull-right">|</li>
52
53 <li class="pull-right"> <a href="index.html" title="Documentation Home">
54 Documentation Home</a>
55 </li>
56
57 </ul>
58 </div>
59
60
61 <div class="row-fluid">
62 <div id="leftColumn" class="span3">
63 <div class="well sidebar-nav">
64
65
66 <ul class="nav nav-list">
67 <li class="nav-header">Get Started - Installation</li>
68
69 <li>
70
71 <a href="ncservice.html" title="Option 1: using NCService">
72 <i class="none"></i>
73 Option 1: using NCService</a>
74 </li>
75
76 <li>
77
78 <a href="install.html" title="Option 2: using Managix">
79 <i class="none"></i>
80 Option 2: using Managix</a>
81 </li>
82
83 <li>
84
85 <a href="yarn.html" title="Option 3: using YARN">
86 <i class="none"></i>
87 Option 3: using YARN</a>
88 </li>
89 <li class="nav-header">AsterixDB Primer</li>
90
91 <li>
92
93 <a href="sqlpp/primer-sqlpp.html" title="Option 1: using SQL++">
94 <i class="none"></i>
95 Option 1: using SQL++</a>
96 </li>
97
98 <li>
99
100 <a href="aql/primer.html" title="Option 2: using AQL">
101 <i class="none"></i>
102 Option 2: using AQL</a>
103 </li>
104 <li class="nav-header">Data Model</li>
105
106 <li>
107
108 <a href="datamodel.html" title="The Asterix Data Model">
109 <i class="none"></i>
110 The Asterix Data Model</a>
111 </li>
112 <li class="nav-header">Queries - SQL++</li>
113
114 <li>
115
116 <a href="sqlpp/manual.html" title="The SQL++ Query Language">
117 <i class="none"></i>
118 The SQL++ Query Language</a>
119 </li>
120
121 <li>
122
123 <a href="sqlpp/builtins.html" title="Builtin Functions">
124 <i class="none"></i>
125 Builtin Functions</a>
126 </li>
127 <li class="nav-header">Queries - AQL</li>
128
129 <li>
130
131 <a href="aql/manual.html" title="The Asterix Query Language (AQL)">
132 <i class="none"></i>
133 The Asterix Query Language (AQL)</a>
134 </li>
135
136 <li>
137
138 <a href="aql/builtins.html" title="Builtin Functions">
139 <i class="none"></i>
140 Builtin Functions</a>
141 </li>
142 <li class="nav-header">Advanced Features</li>
143
144 <li>
145
146 <a href="aql/similarity.html" title="Support of Similarity Queries">
147 <i class="none"></i>
148 Support of Similarity Queries</a>
149 </li>
150
151 <li>
152
153 <a href="aql/fulltext.html" title="Support of Full-text Queries">
154 <i class="none"></i>
155 Support of Full-text Queries</a>
156 </li>
157
158 <li>
159
160 <a href="aql/externaldata.html" title="Accessing External Data">
161 <i class="none"></i>
162 Accessing External Data</a>
163 </li>
164
165 <li>
166
167 <a href="feeds/tutorial.html" title="Support for Data Ingestion">
168 <i class="none"></i>
169 Support for Data Ingestion</a>
170 </li>
171
172 <li>
173
174 <a href="udf.html" title="User Defined Functions">
175 <i class="none"></i>
176 User Defined Functions</a>
177 </li>
178
179 <li>
180
181 <a href="aql/filters.html" title="Filter-Based LSM Index Acceleration">
182 <i class="none"></i>
183 Filter-Based LSM Index Acceleration</a>
184 </li>
185 <li class="nav-header">API/SDK</li>
186
187 <li>
188
189 <a href="api.html" title="HTTP API">
190 <i class="none"></i>
191 HTTP API</a>
192 </li>
193 </ul>
194
195
196
197 <hr class="divider" />
198
199 <div id="poweredBy">
200 <div class="clear"></div>
201 <div class="clear"></div>
202 <div class="clear"></div>
203 <a href="./" title="AsterixDB" class="builtBy">
204 <img class="builtBy" alt="AsterixDB" src="images/asterixlogo.png" />
205 </a>
206 </div>
207 </div>
208 </div>
209
210
211 <div id="bodyColumn" class="span9" >
212
213 <!-- ! Licensed to the Apache Software Foundation (ASF) under one
214 ! or more contributor license agreements. See the NOTICE file
215 ! distributed with this work for additional information
216 ! regarding copyright ownership. The ASF licenses this file
217 ! to you under the Apache License, Version 2.0 (the
218 ! "License"); you may not use this file except in compliance
219 ! with the License. You may obtain a copy of the License at
220 !
221 ! http://www.apache.org/licenses/LICENSE-2.0
222 !
223 ! Unless required by applicable law or agreed to in writing,
224 ! software distributed under the License is distributed on an
225 ! "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
226 ! KIND, either express or implied. See the License for the
227 ! specific language governing permissions and limitations
228 ! under the License.
229 ! --><h1>CSV Support in AsterixDB</h1>
230<div class="section">
231<h2><a name="Introduction_-_Defining_a_datatype_for_CSV"></a>Introduction - Defining a datatype for CSV</h2>
232<p>AsterixDB supports the CSV format for both data input and query result output. In both cases, the structure of the CSV data must be defined using a named ADM object datatype. The CSV format, limitations, and MIME type are defined by <a class="externalLink" href="https://tools.ietf.org/html/rfc4180">RFC 4180</a>.</p>
233<p>CSV is not as expressive as the full Asterix Data Model, meaning that not all data which can be represented in ADM can also be represented as CSV. So the form of this datatype is limited. First, obviously it may not contain any nested objects or lists, as CSV has no way to represent nested data structures. All fields in the object type must be primitive. Second, the set of supported primitive types is limited to numerics (<tt>int8</tt>, <tt>int16</tt>, <tt>int32</tt>, <tt>int64</tt>, <tt>float</tt>, <tt>double</tt>) and <tt>string</tt>. On output, a few additional primitive types (<tt>boolean</tt>, datetime types) are supported and will be represented as strings.</p>
234<p>For the purposes of this document, we will use the following dataverse and datatype definitions:</p>
235
236<div class="source">
237<div class="source">
238<pre>drop dataverse csv if exists;
239create dataverse csv;
240use dataverse csv;
241
242create type &quot;csv_type&quot; as closed {
243 &quot;id&quot;: int32,
244 &quot;money&quot;: float,
245 &quot;name&quot;: string
246};
247
248create dataset &quot;csv_set&quot; (&quot;csv_type&quot;) primary key &quot;id&quot;;
249</pre></div></div>
250<p>Note: There is no explicit restriction against using an open datatype for CSV purposes, and you may have optional fields in the datatype (eg., <tt>id: int32?</tt>). However, the CSV format itself is rigid, so using either of these datatype features introduces possible failure modes on output which will be discussed below.</p></div>
251<div class="section">
252<h2><a name="CSV_Input"></a>CSV Input</h2>
253<p>CSV data may be loaded into a dataset using the normal &#x201c;load dataset&#x201d; mechanisms, utilizing the builtin &#x201c;delimited-text&#x201d; format. See <a href="aql/externaldata.html">Accessing External Data</a> for more details. Note that comma is the default value for the &#x201c;delimiter&#x201d; parameter, so it does not need to be explicitly specified.</p>
254<p>In this case, the datatype used to interpret the CSV data is the datatype associated with the dataset being loaded. So, to load a file that we have stored locally on the NC into our example dataset:</p>
255
256<div class="source">
257<div class="source">
258<pre>use dataverse csv;
259
260load dataset &quot;csv_set&quot; using localfs
261((&quot;path&quot;=&quot;127.0.0.1:///tmp/my_sample.csv&quot;),
262 (&quot;format&quot;=&quot;delimited-text&quot;));
263</pre></div></div>
264<p>So, if the file <tt>/tmp/my_sample.csv</tt> contained</p>
265
266<div class="source">
267<div class="source">
268<pre>1,18.50,&quot;Peter Krabnitz&quot;
2692,74.50,&quot;Jesse Stevens&quot;
270</pre></div></div>
271<p>then the preceding query would load it into the dataset <tt>csv_set</tt>.</p>
272<p>If your CSV file has a header (that is, the first line contains a set of field names, rather than actual data), you can instruct Asterix to ignore this header by adding the parameter <tt>&quot;header&quot;=&quot;true&quot;</tt>, eg.</p>
273
274<div class="source">
275<div class="source">
276<pre>load dataset &quot;csv_set&quot; using localfs
277((&quot;path&quot;=&quot;127.0.0.1:///tmp/my_header_sample.csv&quot;),
278 (&quot;format&quot;=&quot;delimited-text&quot;),
279 (&quot;header&quot;=&quot;true&quot;));
280</pre></div></div>
281<p>CSV data may also be loaded from HDFS; see <a href="aql/externaldata.html">Accessing External Data</a> for details. However please note that CSV files on HDFS cannot have headers. Attempting to specify &#x201c;header&#x201d;=&#x201c;true&#x201d; when reading from HDFS could result in non-header lines of data being skipped as well.</p></div>
282<div class="section">
283<h2><a name="CSV_Output"></a>CSV Output</h2>
284<p>Any query may be rendered as CSV when using AsterixDB&#x2019;s HTTP interface. To do so, there are two steps required: specify the object type which defines the schema of your CSV, and request that Asterix use the CSV output format.</p>
285<div class="section">
286<div class="section">
287<h4><a name="Output_Object_Type"></a>Output Object Type</h4>
288<p>Background: The result of any AQL query is an unordered list of <i>instances</i>, where each <i>instance</i> 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:</p>
289
290<ol style="list-style-type: decimal">
291
292<li>Each instance must be of a object type.</li>
293
294<li>Each instance must be of the <i>same</i> object type.</li>
295
296<li>The object type must conform to the content and type restrictions mentioned in the introduction.</li>
297</ol>
298<p>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 <tt>set</tt> statement prior to the query to set the parameter <tt>output-record-type</tt> to the name of an ADM type. This type must have already been defined in the current dataverse.</p>
299<p>For example, the following request will ensure that all result instances are cast to the <tt>csv_type</tt> type declared earlier:</p>
300
301<div class="source">
302<div class="source">
303<pre>use dataverse csv;
304set output-record-type &quot;csv_type&quot;;
305
306for $n in dataset &quot;csv_set&quot; return $n;
307</pre></div></div>
308<p>In this case the casting is redundant since by definition every value in <tt>csv_set</tt> is already of type <tt>csv_type</tt>. But consider a more complex query where the result values are created by joining fields from different underlying datasets, etc.</p>
309<p>Two notes about <tt>output-record-type</tt>:</p>
310
311<ol style="list-style-type: decimal">
312
313<li>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).</li>
314
315<li>When the CSV output format is requested, <tt>output-record-type</tt> 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).</li>
316</ol></div>
317<div class="section">
318<h4><a name="Request_the_CSV_Output_Format"></a>Request the CSV Output Format</h4>
319<p>When sending requests to the Asterix HTTP API, Asterix decides what format to use for rendering the results in one of two ways:</p>
320
321<ul>
322
323<li>
324<p>A HTTP query parameter named &#x201c;output&#x201d;, which must be set to one of the following values: <tt>JSON</tt>, <tt>CSV</tt>, or <tt>ADM</tt>.</p></li>
325
326<li>
327<p>Based on the <a class="externalLink" href="http://www.w3.org/Protocols/rfc2616/rfc2616-sec14.html#sec14.1"><tt>Accept</tt> HTTP header</a></p></li>
328</ul>
329<p>By default, Asterix will produce JSON output. To select CSV output, pass the parameter <tt>output=CSV</tt>, or set the <tt>Accept</tt> header on your request to the MIME type <tt>text/csv</tt>. 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 &#x201c;curl&#x201d; and specifying the &quot;output query parameter:</p>
330
331<div class="source">
332<div class="source">
333<pre>curl -G &quot;http://localhost:19002/query&quot; \
334 --data-urlencode 'output=CSV' \
335 --data-urlencode 'query=use dataverse csv;
336 set output-record-type &quot;csv_type&quot;;
337 for $n in dataset csv_set return $n;'
338</pre></div></div>
339<p>Alternately, the same query using the <tt>Accept</tt> header:</p>
340
341<div class="source">
342<div class="source">
343<pre>curl -G -H &quot;Accept: text/csv&quot; &quot;http://localhost:19002/query&quot; \
344 --data-urlencode 'query=use dataverse csv;
345 set output-record-type &quot;csv_type&quot;;
346 for $n in dataset csv_set return $n;'
347</pre></div></div>
348<p>Similarly, a trivial Java program to execute the above sample query and selecting CSV output via the <tt>Accept</tt> header would be:</p>
349
350<div class="source">
351<div class="source">
352<pre>import java.net.HttpURLConnection;
353import java.net.URL;
354import java.net.URLEncoder;
355import java.io.BufferedReader;
356import java.io.InputStream;
357import java.io.InputStreamReader;
358
359public class AsterixExample {
360 public static void main(String[] args) throws Exception {
361 String query = &quot;use dataverse csv; &quot; +
362 &quot;set output-record-type \&quot;csv_type\&quot;;&quot; +
363 &quot;for $n in dataset csv_set return $n&quot;;
364 URL asterix = new URL(&quot;http://localhost:19002/query?query=&quot; +
365 URLEncoder.encode(query, &quot;UTF-8&quot;));
366 HttpURLConnection conn = (HttpURLConnection) asterix.openConnection();
367 conn.setRequestProperty(&quot;Accept&quot;, &quot;text/csv&quot;);
368 BufferedReader result = new BufferedReader
369 (new InputStreamReader(conn.getInputStream()));
370 String line;
371 while ((line = result.readLine()) != null) {
372 System.out.println(line);
373 }
374 result.close();
375 }
376}
377</pre></div></div>
378<p>For either of the above examples, the output would be:</p>
379
380<div class="source">
381<div class="source">
382<pre>1,18.5,&quot;Peter Krabnitz&quot;
3832,74.5,&quot;Jesse Stevens&quot;
384</pre></div></div>
385<p>assuming you had already run the previous examples to create the dataverse and populate the dataset.</p></div>
386<div class="section">
387<h4><a name="Outputting_CSV_with_a_Header"></a>Outputting CSV with a Header</h4>
388<p>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:</p>
389
390<ul>
391
392<li>
393<p>By passing the HTTP query parameter &#x201c;header&#x201d; with the value &#x201c;present&#x201d;</p></li>
394
395<li>
396<p>By specifying the MIME type {{text/csv; header=present}} in your HTTP Accept: header. This is consistent with RFC 4180.</p></li>
397</ul></div>
398<div class="section">
399<h4><a name="Issues_with_open_datatypes_and_optional_fields"></a>Issues with open datatypes and optional fields</h4>
400<p>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.</p>
401<p>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 <tt>null</tt>.</p>
402<p>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.</p>
403<p>According to RFC 4180 this is not strictly valid CSV (Section 2, rule 4, &#x201c;Each line <i>should</i> contain the same number of fields throughout the file&#x201d;). 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 <tt>load dataset</tt>, 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.</p></div></div></div>
404 </div>
405 </div>
406 </div>
407
408 <hr/>
409
410 <footer>
411 <div class="container-fluid">
412 <div class="row span12">Copyright &copy; 2017
413 <a href="https://www.apache.org/">The Apache Software Foundation</a>.
414 All Rights Reserved.
415
416 </div>
417
418 <?xml version="1.0" encoding="UTF-8"?>
419<div class="row-fluid">Apache AsterixDB, AsterixDB, Apache, the Apache
420 feather logo, and the Apache AsterixDB project logo are either
421 registered trademarks or trademarks of The Apache Software
422 Foundation in the United States and other countries.
423 All other marks mentioned may be trademarks or registered
424 trademarks of their respective owners.</div>
425
426
427 </div>
428 </footer>
429 </body>
430</html>