Blame - csv.html - incubator-asterixdb-site

<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 record 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>

205

<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 records or lists, as CSV has no way to represent nested data structures. All fields in the record 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>

206

<p>For the purposes of this document, we will use the following dataverse and datatype definitions:</p>

207

208

209

<pre>drop dataverse csv if exists;

210

create dataverse csv;

211

use dataverse csv;

212

213

create type "csv_type" as closed {

"id": int32,

"money": float,

"name": string

};

create dataset "csv_set" ("csv_type") primary key "id";

220

</pre></div>

221

<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>

222

223

<h2>CSV Input<a name="CSV_Input"></a></h2>

224

<p>CSV data may be loaded into a dataset using the normal “load dataset” mechanisms, utilizing the builtin “delimited-text” format. See <a href="aql/externaldata.html">Accessing External Data</a> for more details. Note that comma is the default value for the “delimiter” parameter, so it does not need to be explicitly specified.</p>

225

<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>

226

227

228

<pre>use dataverse csv;

229

230

load dataset "csv_set" using localfs

231

(("path"="127.0.0.1:///tmp/my_sample.csv"),

232

("format"="delimited-text"));

233

</pre></div>

234

<p>So, if the file <tt>/tmp/my_sample.csv</tt> contained</p>

235

236

237

<pre>1,18.50,"Peter Krabnitz"

238

2,74.50,"Jesse Stevens"

239

</pre></div>

240

<p>then the preceding query would load it into the dataset <tt>csv_set</tt>.</p>

241

<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>"header"="true"</tt>, eg.</p>

242

243

244

<pre>load dataset "csv_set" using localfs

245

(("path"="127.0.0.1:///tmp/my_header_sample.csv"),

246

("format"="delimited-text"),

247

("header"="true"));

248

</pre></div>

249

<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 “header”=“true” when reading from HDFS could result in non-header lines of data being skipped as well.</p></div>

250

251

<h2>CSV Output<a name="CSV_Output"></a></h2>

252

<p>Any query may be rendered as CSV when using AsterixDB’s HTTP interface. To do so, there are two steps required: specify the record type which defines the schema of your CSV, and request that Asterix use the CSV output format.</p>

253

254

255

<h4>Output Record Type<a name="Output_Record_Type"></a></h4>

256

<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>

<li>Each instance must be of a record type.</li>

261

262

<li>Each instance must be of the <i>same</i> record type.</li>

263

264

<li>The record type must conform to the content and type restrictions mentioned in the introduction.</li>

265

</ol>

266

<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 record 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>

267

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

268

269

270

<pre>use dataverse csv;

271

set output-record-type "csv_type";

272

273

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

274

</pre></div>

275

<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>

276

<p>Two notes about <tt>output-record-type</tt>:</p>

<li>This feature is not strictly related to CSV; it may be used with any output formats (in which case, any record datatype may be specified, not subject to the limitations specified in the introduction of this page).</li>

281

282

<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>

283

</ol></div>

284

285

<h4>Request the CSV Output Format<a name="Request_the_CSV_Output_Format"></a></h4>

286

<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>

<ul>

<li>

<p>A HTTP query parameter named “output”, which must be set to one of the following values: <tt>JSON</tt>, <tt>CSV</tt>, or <tt>ADM</tt>.</p></li>

292

293

<li>

294

<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>

295

</ul>

296

<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 “curl” and specifying the "output query parameter:</p>

297

298

299

<pre>curl -G "http://localhost:19002/query" \

300

--data-urlencode 'output=CSV' \

301

--data-urlencode 'query=use dataverse csv;

302

set output-record-type "csv_type";

303

for $n in dataset csv_set return $n;'

304

</pre></div>

305

<p>Alternately, the same query using the <tt>Accept</tt> header:</p>

306

307

308

<pre>curl -G -H "Accept: text/csv" "http://localhost:19002/query" \

309

--data-urlencode 'query=use dataverse csv;

310

set output-record-type "csv_type";

311

for $n in dataset csv_set return $n;'

312

</pre></div>

313

<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>

314

315

316

<pre>import java.net.HttpURLConnection;

317

import java.net.URL;

318

import java.net.URLEncoder;

319

import java.io.BufferedReader;

320

import java.io.InputStream;

321

import java.io.InputStreamReader;

322

323

public class AsterixExample {

324

public static void main(String[] args) throws Exception {

325

String query = "use dataverse csv; " +

326

"set output-record-type \"csv_type\";" +

327

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

328

URL asterix = new URL("http://localhost:19002/query?query=" +

329

URLEncoder.encode(query, "UTF-8"));

330

HttpURLConnection conn = (HttpURLConnection) asterix.openConnection();

331

conn.setRequestProperty("Accept", "text/csv");

332

BufferedReader result = new BufferedReader

333

(new InputStreamReader(conn.getInputStream()));

334

String line;

335

while ((line = result.readLine()) != null) {

336

System.out.println(line);

}

result.close();

}

}

</pre></div>

<p>For either of the above examples, the output would be:</p>

343

344

345

<pre>1,18.5,"Peter Krabnitz"

346

2,74.5,"Jesse Stevens"

347

</pre></div>

348

<p>assuming you had already run the previous examples to create the dataverse and populate the dataset.</p></div>

349

350

<h4>Outputting CSV with a Header<a name="Outputting_CSV_with_a_Header"></a></h4>

351

<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>

<ul>

<li>

<p>By passing the HTTP query parameter “header” with the value “present”</p></li>

357

358

<li>

359

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

360

</ul></div>

361

362

<h4>Issues with open datatypes and optional fields<a name="Issues_with_open_datatypes_and_optional_fields"></a></h4>

363

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

364

<p>If your output record 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>

365

<p>If your output record 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>

366

<p>According to RFC 4180 this is not strictly valid CSV (Section 2, rule 4, “Each line <i>should</i> 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 <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 record types. AsterixDB allows to use an open record type only to support cases where the type already exists for other parts of your application.</p></div></div></div>

</div>

</div>

</div>

<hr/>