Importer

ArcadeDB provides some basic ETL capabilities for automatically importing datasets in any of the following formats:

  • OrientDB database export

  • Neo4j database export

  • GraphML database export

  • GraphSON database export

  • Generic XML files

  • Generic JSON files

  • Generic JSONL files

  • Generic CSV files

  • Generic RDF files

From file of types:

  • Plain text

  • Compressed with ZIP (the first entry in the archive is read, unless you select one - see below)

  • Compressed with GZip

Located on:

  • local file system (just provide the path or use file:// in the URL)

  • remote, by specifying http:// or https:// in the URL

  • classpath, by using classpath:// as a prefix

(Since v26.10.1: format detection on a remote (http:///https://) source could stop reading in the middle of the stream on a slow or chunked connection, and guess the file’s format from a partial first line. A local file was never affected. One consequence of the fix: a remote source that stops sending data without closing the connection now makes the import wait rather than fail immediately - there is no read timeout yet.)

To import one specific entry out of a ZIP archive that contains several, append ::: and the entry name to the URL:

IMPORT DATABASE file://data.zip:::nodes.csv

Without :::, the first entry in the archive is imported.

The easiest way is to use the console and the SQL IMPORT DATABASE command. You can also use directly the Java API located in com.arcadedb.integration.importer.Importer.

To start importing it’s super easy as providing the URL where the source file to import is located. URLs can be local paths (use file://) or from the Internet by using http:// and https://.

For security reasons, importing from an http:///https:// URL refuses by default to reach a local-file, loopback, link-local, or private-network address, including a redirect that lands on one. This protects the server from being made to fetch from internal-only hosts (e.g. a cloud metadata endpoint) via a crafted or hijacked import URL. Running IMPORT DATABASE directly (from the console or the SQL API) is gated by arcadedb.server.security.importBlockLocalNetworks, which defaults to true; set it to false on the server if you need to import from such an address (for example, an internal artifact server). The import database <dbname> <url> server command is gated instead by arcadedb.server.restoreImportAllowLocalUrls - the same setting documented for restoring a database - which must be set to true for the equivalent override.

Example of loading the Freebase RDF dataset:

> CREATE DATABASE FreeBase
{FreeBase}> IMPORT DATABASE http://commondatastorage.googleapis.com/freebase-public/rdf/freebase-rdf-latest.gz
Analyzing url: http://commondatastorage.googleapis.com/freebase-public/rdf/freebase-rdf-latest.gz... [SourceDiscovery]
Recognized format RDF (limitBytes=9.54MB limitEntries=0) [SourceDiscovery]
Creating type 'Node' of type VERTEX [Importer]
Creating type 'Relationship' of type EDGE [Importer]
Parsed 144951 (28990/sec) - 0 documents (0/sec) - 143055 vertices (28611/sec) - 144951 edges (28990/sec) [Importer]
Parsed 362000 (54256/sec) - 0 documents (0/sec) - 164118 vertices (5260/sec) - 362000 edges (54256/sec) [Importer]
...

An RDF source is recognised from the shape of its first statement - subject predicate object with an optional closing . - so quoted literal objects, language tags (@en), typed literals (^^<datatype>), blank nodes (:b1) and Windows (CRLF) line endings are all read as RDF, and the character separating the terms is used as the field delimiter. _(Since v26.10.1: a file using any of those was analysed as delimited text instead and the import failed with a number-parsing error naming an IRI. If a file still cannot be recognised, set the separator explicitly with the delimiter setting.)

Every line of an RDF source is data: unlike CSV, these formats have no header row, so nothing is skipped unless you ask for it - with edgesSkipEntries on the edges route, verticesSkipEntries on vertices, or documentsSkipEntries on documents and on a plain url import. (Since v26.10.1: the first statement of every RDF file was dropped as if it were a header, so N triples produced N-1 edges. If you were passing edgesSkipEntries = 0 to work around that, you can remove it. Also since v26.10.1: an RDF file imported through vertices or documents only honoured edgesSkipEntries, ignoring the matching option for that route.)

Lines at the top of a source that start with # or // are treated as comments and ignored, for every format - CSV, RDF, XML, JSON and JSONL alike. (Since v26.10.1: the comment was examined as if it were the first line of data, which could make the file be recognised as the wrong format and fail to import. Also since v26.10.1: once recognised, the comment lines were still handed to the importer for every format except CSV and RDF, and could make XML and JSON imports fail.)

Example of loading the Discogs dataset in the database on path "/temp/discogs":

> IMPORT DATABASE https://discogs-data.s3-us-west-2.amazonaws.com/data/2018/discogs_20180901_releases.xml.gz

Note that in this case the URL is https and the file is compressed with GZip.

Example of importing New York Taxi dataset in CSV format. The first line of the CSV file set the property names:

> IMPORT DATABASE file:///personal/Downloads/data-society-uber-pickups-in-nyc/original/uber-raw-data-april-15.csv/uber-raw-data-april-15.csv

See also:

Additional Settings

The Importer takes additional settings as pairs of setting name and value. With the SQL IMPORT DATABASE command, this is the syntax:

IMPORT DATABASE <url> [ WITH ( <setting-name> = <setting-value> [,] )* ]

Example:

> IMPORT DATABASE file:///import/file.csv WITH forceDatabaseCreate = true, commitEvery = 100

Transactions

By default an import manages its own transactions, committing every commitEvery records, so nothing is left open when it finishes.

If a transaction is already open when the import starts - because you are using the Java API against your own Database, or you ran IMPORT DATABASE inside an explicit transaction - the import does not take it over. It adds the imported records to your transaction and leaves it as it found it: still open, still yours to commit or roll back. Nothing is made durable until you commit, and rolling back discards the imported records along with your own pending changes.

In that case commitEvery has no effect, since the import commits nothing of its own. For large imports prefer letting the import manage its own transaction, so it can commit in batches instead of holding everything in memory until you commit.

Import report

An import returns a summary of what it did, with the counters that apply to the source: parsedRecords, createdDocuments, createdVertices, createdEdges, skippedRecords, skippedEdges, errors and warnings. A counter is left out of the summary when it is zero.

parsedRecords counts every row read from every file of the import, including any header row and any row that was skipped. It is therefore normally higher than the number of records created. (Since v26.10.1: when an import read more than one file - for instance vertices and edges - this counted only the last file’s rows for most formats, so the same import could report the figure two different ways depending on which file came last.)

skippedRecords counts rows that were skipped on purpose - by a *SkipEntries setting, or by the default header-row skip - and skippedEdges counts edges that were not created because their from or to reference did not match any vertex. Neither is counted as an error: parsedRecords equals createdDocuments + createdVertices
createdEdges + skippedRecords + skippedEdges + errors. (New in v26.10.1: before this, a row missing from the created count for either of these two reasons looked identical to a row dropped by onRowError = skip, and the usual explanation - "the file has a bad row" - was wrong for the first two.)

Below you can find all the supported settings for the Importer.

Setting Default Description

url

url of the file to import

database

./databases/imported

Path of the final imported database

forceDatabaseCreate

false

If true, the database is created brand new at every import

wal

false

Use the WAL (journal) for the importing. If the WAL is enabled the importing process will be much slower and will require much more RAM

commitEvery

5000

Create transactions that commit every X records

onRowError

abort

abort fails the whole import as soon as one record (document, vertex or edge) cannot be imported. skip logs the error, counts it in the errors result, and continues with the next record instead - trading import throughput (each record then commits in its own transaction) for the guarantee that a bad record can never take a good one down with it, nor leave a partially-written record behind. Applies to the JSON, JSONL and CSV importers.

parallel

Half of the available cores - 1. If you have 8 cores, the default is 3

The number of concurrent threads used

typeIdProperty

Property that represents the ID of the vertex

typeIdUnique

false

True creates a unique index on the type id property, otherwise a non unique index

typeIdType

String

Type of the id property

trimText

true

True if the imported text is trimmed from heading and tailing spaces

maxProperties

512

Maximum number of properties per type (CSV)

maxPropertySize

4096

Maximum size of a property in bytes (CSV)

delimiter

,

Delimiter used to separate fields (CSV). When not set, it is auto-detected from the first line for files whose name does not end in .csv. A delimiter set explicitly always wins over the detected one; the precedence is the per-entity delimiter (documentsDelimiter, verticesDelimiter, edgesDelimiter), then delimiter, then the detected one. Each file of the same import resolves its own delimiter

analysisLimitBytes

100,000

Maximum number of bytes parsed from the source to determine the source file type. (Since v26.10.1 also applies to XML sources; before it was CSV-only and XML analysis was unbounded)

analysisLimitEntries

10,000

Maximum number of entries (if applicable) parsed from the source to determine the source file type. (Since v26.10.1 also applies to XML sources)

parsingLimitBytes

Maximum number of bytes parsed from the source to be imported. (Since v26.10.1 enforced on every format; before it had no effect at all)

parsingLimitEntries

Maximum number of entries imported. (Since v26.10.1 this is a cap of exactly N entries, enforced on every format; before it only worked on XML, and there it imported N+1)

mapping

null

probeOnly

false

Only probe if url is reachable or file path is readable

documents

url of the file to import containing documents only. This is useful when the database is split in separate files

documentsFileType

The format of the file containing documents (csv, graphml, graphson)

documentsDelimiter

Delimiter used to separate documents

documentsHeader

Header containing the properties in the CSV document. One property per column. If not defined it is parsed from the first line

documentsSkipEntries

Number of rows to skip from the documents file. When not set, the first line is skipped as the header for delimited text, and nothing is skipped for RDF sources, which have no header row

documentPropertiesInclude

*

List of property to import from documents. * means all

documentType

Document

Name of the type defined in the schema when importing documents

vertices

url of the file to import containing vertices only. This is useful when the database is split in separate files

verticesFileType

The format of the file containing vertices (csv, graphml, graphson)

verticesDelimiter

Delimiter used to separate vertices

verticesHeader

Header containing the properties in the CSV vertices. One property per column. If not defined it is parsed from the first line

verticesSkipEntries

Number of rows to skip from the vertices file. When not set, the first line is skipped as the header for delimited text, and nothing is skipped for RDF sources, which have no header row

expectedVertices

0

Number of vertices expected. This is useful to determine the ETA of the importing process of vertices. 0 means unknown

vertexType

Vertex

Name of the type defined in the schema when importing vertices

vertexPropertiesInclude

*

List of property to import from vertices. * means all

edges

url of the file to import containing edges only. This is useful when the database is split in separate files

edgesFileType

The format of the file containing edges (csv, graphml, graphson)

edgesDelimiter

Delimiter used to separate edges

edgesHeader

Header containing the properties in the CSV edges. One property per column. If not defined it is parsed from the first line

edgesSkipEntries

Number of rows to skip from the edges file. When not set, the first line is skipped as the header for delimited text, and nothing is skipped for RDF sources, which have no header row

expectedEdges

0

Number of edges expected. This is useful to determine the ETA of the importing process of edges. 0 means unknown

maxRAMIncomingEdges

256MB

Maximum RAM used to create edges. The more RAM, the faster.

edgeType

Edge

Name of the type defined in the schema when importing edges

edgePropertiesInclude

*

List of property to import from edges. * means all

edgeFromField

Name of the property containing the starting vertex

edgeToField

Name of the property containing the ending vertex

edgeBidirectional

true

When creating edges, create bidirectional edges if true, otherwise unidirectional

distanceFunction

innerproduct

Type of distance measure, see similarity measures.

efConstruction

256

Size of dynamic neighbor candidate list of (during insert).

ef

256

Number of nearest neighbors to return (in layer search).

m

16

Maximum number of connections per layer in the HNSW index. Higher values improve recall but increase memory usage

vectorType

float

The data type of a vector element, for example 'float'.

idProperty

"name"

Name of the property that will be used as the unique identifier for vertices during import
The probeOnly setting can also be used to send a GET request to another service or HTTP API, for example to report a previous import is finished.