Importer
ArcadeDB provides some basic ETL capabilities for automatically importing datasets in any of the following formats:
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://orhttps://in the URL -
classpath, by using
classpath://as a prefix
A remote source that stops sending data without closing the connection 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 restore an export into a database you reach over the network, send
|
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.
|
the import: startup command of arcadedb.server.defaultDatabases obeys arcadedb.server.security.importBlockLocalNetworks as set on that server.
|
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. If a file still cannot be recognised, set the separator explicitly with
the delimiter setting.
Each statement becomes a Relationship edge, with the predicate in its label property, between two Node vertices
keyed by the typeIdProperty (id by default). If the database has no Node type, the import creates it with a
unique index on that property. An IRI is keyed by its text without the angle brackets and a blank node by its label
(_:b1). A literal object is keyed by its canonical N-Triples form, quotes included ("hello world",
"bonjour"@fr, "42"^^http://www.w3.org/2001/XMLSchema#integer), so it never shares a vertex with an IRI of the same
text, and "a" and "a"^^http://www.w3.org/2001/XMLSchema#string are the same literal. The vertex of a literal also
gets value, its text with escapes such as é and \n decoded and without quotes, plus lang for a
language-tagged literal and datatype for a typed one. A malformed statement, or a term longer than
maxPropertySize, is a row error that onRowError = skip can skip. In a comma-delimited triple file a quoted cell is
an N-Triples literal, not a CSV field. (Since v26.12.1: before, a plain literal was keyed without its quotes and could
merge with the IRI of the same text, the empty literal "" was stored as ., escapes were not decoded, and an import
into a database without the Node type failed. A database filled by an older version keeps the old unquoted keys, so
importing the same data into it again stores those literals a second time under the new form.)
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.
If you supply the column names yourself - with documentsHeader, verticesHeader or edgesHeader - the file is
taken to have no header row, so its first line is imported as data.
Setting a …SkipEntries option explicitly always wins over both defaults.
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.
A JSON source is recognised by its first character that is not whitespace: { for an object, [ for an array of
records. A source whose name ends in .json or .xml but whose content does not begin like that format is refused,
naming the extension, rather than analysed as delimited text; if the content is what you meant, set the format
explicitly with fileType (or documentsFileType on the documents route). (Since v26.11.1: a top-level JSON array
was not recognised, so a one-line array was imported as a CSV file whose header was the whole array, creating nothing
while reporting success, and a JSON document opening with whitespace was not recognised either.)
An ArcadeDB export is recognised by its first line, the "info" header the exporter writes, whatever the file is named:
backup.tgz imports like backup.jsonl.tgz. A JSON source holds one top-level value, so a source with more content
after its first object or array, such as newline-delimited JSON that is not an ArcadeDB export, is refused rather than
imported in part; wrap its objects in an array. An ArcadeDB export forced to fileType = 'json' is refused naming
jsonl. (Since v26.12.1, issue #11729: only the .jsonl extension recognised an export, so one named backup.tgz was
read as JSON: its header line was imported as one Document, every record after it was dropped, and the import reported
OK.)
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:
-
Graph Importer (high-performance, declarative graph import from XML, CSV, JSONL with JSON configuration)
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.
Over HTTP, IMPORT DATABASE sent to POST /api/v1/command runs without the transaction the server otherwise opens around
every command, so it commits in batches like the console does. Inside a session transaction (/begin … /commit), or with
"autoCommit": true in the request, it is added to that transaction as described above. (Since v26.12.1, issue #11728: the
transaction the server opened around the command was taken for yours, so the whole import was held in memory until it
ended and a large one could run the server out of heap; "autoCommit": false was the workaround.)
An import that fails still leaves your transaction alone: it discards only its own pending work and reports the error.
(Since v26.10.2: a GraphML import, or a GraphSON import with RID ids, committed your open transaction, so your
rollback did not take the imported records back. A standalone GraphML or GraphSON import that failed could leave its
last batch open on the thread instead of rolling it back.)
(Since v26.12.1, issue #11271: a JSONL import whose commit was refused - a rolled-back-only transaction, a server no longer
accepting writes - left its transaction open on the thread, so your next begin()/commit() pair nested inside it. It is
now rolled back. With onRowError = skip, a JSONL record whose own commit is refused, for example by a duplicate key, is
skipped and counted in errors instead of failing the whole import, and a later edge or link naming it is treated as
naming a record that is not in the file.)
CSV rows and the header
A CSV row is matched against the header by column count.
A row with fewer columns than the header is imported with the columns it does supply; the remaining properties are
left unset, and the row is counted under warnings.
A row with more columns than the header cannot be imported as it stands - the extra values have no field name to be
stored under - so by default the import fails, naming the line and both column counts. Set onRowError to skip to
have such rows skipped and counted under errors instead.
Note the consequence for large files: analysisLimitEntries and
analysisLimitBytes bound only the analysis, so a row with extra columns beyond that window fails the import too.
If the analysis reads only rows it has to refuse, it cannot derive any property and the import fails saying so, naming the analysis limit when one is set - widening it is usually the fix.
Column types of CSV and XML sources
The analysis of a CSV or XML source declares a property LONG when every value of it is the canonical decimal text
of an integer (0, 42, -12), DOUBLE when every value is a plain decimal number (1.5, -0.25, 1e5), and
STRING otherwise. A value that only a programming language reads as a number keeps the column a STRING, so its
text is stored as it is: a leading +, leading zeros (007, the ZIP code 02134), a type suffix (10D, 3F),
hexadecimal (0x1p3), NaN, Infinity, surrounding whitespace, a dangling dot (1.), an integer too large for a
LONG and a number too large for a DOUBLE. (Since v26.11.1: those values were accepted as numbers, so 02134 was
stored as 2134 and 10D as 10.0, with the original text kept nowhere. A column that relied on it - for example
+5-style values - is now imported as STRING; create the property with the type you want before the import to
convert it explicitly.)
TimeSeries samples in a JSONL file
The samples of a TimeSeries type are read back through the conversion every other way of writing to such a type shares
(the line protocol, gRPC and the Prometheus remote write): a value is stored as the type of its column, so text a column
can hold is stored as the value it names - "80" in an INTEGER tag, "false" in a BOOLEAN tag, a date in a DATE
tag. The timestamp is a column like the others: text of digits, or a date, is read as the instant it names. A sample
carrying a value no column can hold is refused by name, as <type>.<column> (tag|field|timestamp): <reason>, and
onRowError decides what follows: by default the import fails and nothing of that chunk of samples is imported; with
skip only the refused samples are dropped and counted under errors, and the others of their chunk are kept.
(Since v26.11.1: the importer handed the engine the raw values of the file, so text for a typed column aborted the
whole import with Failed to append timeseries samples, naming the column but not the sample, and skip dropped the
entire chunk.)
Import report
An import returns a summary of what it did, with the counters that apply to the source: parsedRecords,
createdRecords, createdDocuments, createdVertices, createdEdges, skippedRecords, skippedEdges, errors and
warnings. parsedRecords and createdRecords (the records of every kind the import created) are always present;
every other counter is left out of the summary when it is zero.
A source that holds no records is a valid import, such as a CSV file with only its header, an empty JSON array or an XML
document with only its root element. The import ends with result OK, and warningMessages names the source and the
format it was read as, which is what tells such a source from one read as the wrong format. An import that cannot
have read anything fails instead: one that names no source (url, documents, vertices or edges), a setting that
is not in the list below, an import whose every source has zero bytes, whatever the format, and an HTML page in place of
data, unless it was asked for as XML. A source of zero bytes named beside one that is not empty is skipped, with a message
in warningMessages.
|
(Since v26.12.1.) These four cases used to end with |
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.
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.
Below you can find all the supported settings for the Importer.
| Setting | Default | Description |
|---|---|---|
url |
url of the file to import |
|
fileType |
The format of the |
|
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 |
|
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. Also used by an RDF/N-Triples source to key its subjects and objects, where it defaults to |
|
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). For an RDF source, the maximum length in characters of each term |
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 |
analysisLimitBytes |
100,000 |
Maximum number of bytes parsed from the source to determine the source file type. It also applies to XML sources |
analysisLimitEntries |
10,000 |
Maximum number of entries (if applicable) parsed from the source to determine the source file type. It also applies to XML sources |
parsingLimitBytes |
Maximum number of bytes parsed from the source to be imported. Enforced on every format |
|
parsingLimitEntries |
Maximum number of entries imported. This is a cap of exactly N entries, enforced on every format |
|
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, json, jsonl, xml, 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. When you do define it, the file is assumed to contain no header row, so its first line is imported as data. The names are separated by the delimiter in force, not by a comma |
|
documentsSkipEntries |
Number of rows to skip from the documents file. When not set, the first line is skipped as the header for delimited text - unless you supplied |
|
documentPropertiesInclude |
|
List of property to import from documents. |
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). See |
|
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. When you do define it, the file is assumed to contain no header row, so its first line is imported as data. The names are separated by the delimiter in force, not by a comma |
|
verticesSkipEntries |
Number of rows to skip from the vertices file. When not set, the first line is skipped as the header for delimited text - unless you supplied |
|
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. |
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). See |
|
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. When you do define it, the file is assumed to contain no header row, so its first line is imported as data. The names are separated by the delimiter in force, not by a comma |
|
edgesSkipEntries |
Number of rows to skip from the edges file. When not set, the first line is skipped as the header for delimited text - unless you supplied |
|
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. |
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 |
|
Type of distance measure, see similarity measures. |
beamWidth |
100 |
Size of the candidate list kept while the vector index is built. Higher values improve recall but slow the build |
m |
16 |
Maximum number of connections per layer in the HNSW index. Higher values improve recall but increase memory usage |
vectorType |
|
The data type of a vector element, for example 'float'. |
vectorProperty |
|
Name of the property holding the vector of a Word2Vec or GloVe import. (Since v26.11.1: it had no default, and an import that did not set it failed with a |
idProperty |
|
Name of the property that will be used as the unique identifier for vertices during import. A blank |
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.
|