How to import relational data into Backendless with CSV files

mark-piller · September 9, 2022, 5:41pm

Relational data support is one of the strongest points of the Backendless database system. In some cases, you want to bootstrap your development by bringing some sample (or existing) data into Backendless. This can be done using the import function available on the Manage > Import screen in the Backendless console:

When you import data into Backendless database, a single CSV file brings data into a single database table. If the table does not exist at the time of the import, it will be created with the same name as the CSV file name. Otherwise, the platform will use a table that has the same name as the name of the CSV file.

Individual CSV files can be imported using the BROWSE... button located at the bottom (right where it says Import single file and then Data service. This option supports multiple file selection and is the recommended way to import single or multiple data (CSV) files.

In this article, I would like to review the format of the CSV files for the scenario when your database tables have relations. Consider the following database schema:

The schema features different types of relations, including both one-to-one and one-to-many relations.

Below is the sample data as we expect it to appear in Backendless:

Customer table:

Address table:

Order table:

OrderItem table:

Let’s review the source CSV data used to populate the database:

Customer.csv:

name,phoneNumber,"objectId({""type"":""STRING_ID""})","address({""type"":""RELATION"",""relatedTable"":""Address""})"
"Joe","555-1212","765EF32B-A715-4C54-84D6-EB1E8B622121","F1202A07-3775-4AEE-AC72-9EA0FC1BC54C"
"Bob","555-4343","8D79BA7D-ACE3-47DC-9D90-B5985CDDD363","A36DDF2E-D239-457C-989A-B9F2319A58BC"
"Jack","555-9898","FC584FF8-E44B-408D-B9F4-F7CF387CD538","6F68AE45-43D7-4E3D-92E3-D9DCE37F0EA3"

Address.csv:

city,country,"objectId({""type"":""STRING_ID""})"
"Chicago","United States","3C48AE7A-6D9D-4ABD-9ADF-77DB456BA428"
"New York","United States","6F68AE45-43D7-4E3D-92E3-D9DCE37F0EA3"
"Dallas","United States","A36DDF2E-D239-457C-989A-B9F2319A58BC"
"Miami","United States","F1202A07-3775-4AEE-AC72-9EA0FC1BC54C"

Order.csv

deliveryDate,name,orderId,"priority({""type"":""STRING"",""dataSize"":250,""expression"":""CASE\n  WHEN DAYOFWEEK( deliveryDate ) IN (2,3,4,5,6)   \n    THEN 'HIGH'\n  WHEN DAYOFWEEK( deliveryDate ) IN (1,7)  \n    THEN 'MEDIUM'\nEND"",""dynamicProps"":{""localData"":{}}})","objectId({""type"":""STRING_ID"",""dataSize"":36})","orders({""type"":""RELATION_LIST"",""relatedTable"":""Customer""})"
"2022-03-12T06:00:00.000Z","Order A","050722-AK1","MEDIUM","11F0354B-5F20-4AEE-A511-7E26155DF6BE","765EF32B-A715-4C54-84D6-EB1E8B622121"
"2022-03-24T05:00:00.000Z","Order B","061821-CV1","HIGH","3A04D2B6-44D3-4A08-AC56-A316366C5069","8D79BA7D-ACE3-47DC-9D90-B5985CDDD363"
"2022-05-27T05:00:00.000Z","Order C","090819-CV2","HIGH","4D8BDF3E-ABB7-4EC6-9B46-C235FCB07077","765EF32B-A715-4C54-84D6-EB1E8B622121"

OrderItem.csv

name,quantity,"objectId({""type"":""STRING_ID"",""dataSize"":36})","manufacturerAddress({""type"":""RELATION"",""relatedTable"":""Address"",""relationIdentificationColumn"":""city""})","orderDetails({""type"":""RELATION_LIST"",""relatedTable"":""Order""})"
"Paper Towels",10.0,"43925FB1-1F7A-41E6-B8C5-0FB5F8566FE3","6F68AE45-43D7-4E3D-92E3-D9DCE37F0EA3",null
"Paper Towels",10.0,"43925FB1-1F7A-41E6-B8C5-0FB5F8566FE3",null,"4D8BDF3E-ABB7-4EC6-9B46-C235FCB07077"
"Paper Towels",10.0,"43925FB1-1F7A-41E6-B8C5-0FB5F8566FE3",null,"3A04D2B6-44D3-4A08-AC56-A316366C5069"
"Paper Towels",10.0,"43925FB1-1F7A-41E6-B8C5-0FB5F8566FE3",null,"11F0354B-5F20-4AEE-A511-7E26155DF6BE"
"Bathroom Tissue",20.0,"91026D1C-217E-4078-AEDD-2DBDBD10BF7E","A36DDF2E-D239-457C-989A-B9F2319A58BC",null
"Bathroom Tissue",20.0,"91026D1C-217E-4078-AEDD-2DBDBD10BF7E",null,"4D8BDF3E-ABB7-4EC6-9B46-C235FCB07077"
"Bathroom Tissue",20.0,"91026D1C-217E-4078-AEDD-2DBDBD10BF7E",null,"3A04D2B6-44D3-4A08-AC56-A316366C5069"
"Bathroom Tissue",20.0,"91026D1C-217E-4078-AEDD-2DBDBD10BF7E",null,"11F0354B-5F20-4AEE-A511-7E26155DF6BE"
"Pencils",20.0,"F7A01263-FE23-4ED5-A7C1-342C373BFA4F",null,"3A04D2B6-44D3-4A08-AC56-A316366C5069"
"Pencils",20.0,"F7A01263-FE23-4ED5-A7C1-342C373BFA4F","6F68AE45-43D7-4E3D-92E3-D9DCE37F0EA3",null

A few things about expressing relations in the CSVs to point out:

For the 1:1 relations, the parent table/CSV reference child objects - see Customer.csv. You will find the following column declaration for the 1:1 relation with the Address table:
```
"address({""type"":""RELATION"",""relatedTable"":""Address""})"
```
Values of the child objects for 1:1 relation are specified in the parent CSV file. You will see there are objectId values of the Address objects in Customer.csv.
For the 1:N relations, it is done in reverse - child records reference it’s parent. For example, see Orders.csv. You will see the following column declaration:
```
"orders({""type"":""RELATION_LIST"",""relatedTable"":""Customer""})"
```
Keep in mind that In this case, the column is created/used in the parent table.

Most of the columns declared in the sample CSV files above do not have any additional meta information. In this case, when you import the files, Backendless will prompt you with the following screen where you can specify the data types for each column. For example, below is a screen you will see for Customer.csv referenced above:

A similar screen will be displayed for each imported CSV. Make sure to assign the right data types and click FINALIZE IMPORT.

Alternatively, when you import a single zip file, Backendless will not prompt you to identity data types and will assign the STRING data type to all columns (except for the relations). You are welcome to experiment with the sample data shown above by importing the archive file referenced below:
Sample-Import-Data.zip (3.7 KB)

Hope you found this helpful. Any comments and feedback is always appreciated.

Justin_Saunders · November 27, 2022, 2:34am

Hi Mark,

I am trying to work all this out, seems very complicated for a service that prompts no code. I have used other services that allow you to identify the related table in the import screen, without having to have a different heading.
Is there a tutorial video on this subject that I can watch to get a better understanding of how this is going to work?

Regards

Justin

James_Hereford · April 11, 2024, 12:12am

Hey Mark,

It took me a while to dissect this between this post and the dev support article:
https://backendless.com/docs/rest/mgmt_import.html#data-tables-csv

I confirmed this interpretation through testing but am adding this to triple check my understanding and hopefully help other people starting out.

It sounds like Backendless forces each table’s primary key (PK) to be a field named “objectID” with type STRING_ID. You can define this yourself per the API docs using column declaration “objectId(STRING_ID)”. If you do so it appears you can populate it pretty much anyway you want as long as the values are unique. If you choose not to define it, Backendless will create the field for you and populate it with a unique 32 character alphanumeric string.

When you declare relations between tables, you have to declare the relation using both the header value you described AND populate the data with the corresponding values from the objectID field specifically since that is acting as the PK.

In my case, I had a table that was uniquely defined by zipcode and had planned for zipcode to act as the primary key. I duplicated zipcode into another field with the objectID declaration so that I could still do lookups on that basis.

Two questions:

is all of that understanding correct?
is there another way I could have maintained my ability to relate the tables based on the intuitive primary key (zip code) instead of just duplicating it into the objectID field? (i.e. is there a way to specify the primary key differently, or some other way of referencing the unique values in objectID using another column as a foreign key?) The reason this is important is that in some cases relations are established on business logic and seem much more difficult to reference using a random string.

Thanks!

Alexander_Pavelko · April 11, 2024, 1:31pm

Hello James Hereford,

Yes, your understanding is correct.

Regarding your second question, the only PK allowed is “objectId”.

Regards,
Alexander

James_Hereford · April 11, 2024, 3:51pm

Thanks again. One follow-up question getting back to the challenge of referencing a field with a random ObjectID instead of a more intuitive primary key based on business logic. Is there a way to create the ObjectID as a generated field that will always be equal to the value in the field that we’d prefer to be the primary key?

I feel like I am missing something (probably obvious) about how to to relate two tables using a random key when we only know how they relate based on business logic. It’s like I need to lookup the ObjectID from the business logic PK before I can then use the ObjectID to relate the tables…

mark-piller · April 11, 2024, 3:55pm

The objectId column is used by Backendless to uniquely identify a record. When you import data, you can use your own values for that column. If a value for that column is not specified, one will be generated as a UUID. There is no alternative for another auto-generated naming scheme.

Regards,
Mark

James_Hereford · April 11, 2024, 4:24pm

Thanks for all the quick support and replies! So, in my circumstance:

My app needs to enable users to create new records
Let’s take as an example a table where “zipcode” uniquely defines each row and multiple other tables join on that to get other information (e.g. “city”, “state”). Those other tables don’t really have access to a random ObjectID so they need to use the zipcode as the foreign key.
Let’s also say users are accessing this table via a Data Grid and can add records to the table using that tool.
When users create a new row via the data grid, do I need to instruct them to make sure that they either input the zipcode into a field named “objectID” (they just have to know it’s zipcode) or copy the zipcode over to the other field?

Ah… I think I may have just found my answer. Sorry for the slowness. In this situation is the expected developer path that I would change the visible column name to the user to be “Zipcode”, such that they know what data to put in but on the backend in the table that column is actually the objectID field?

Thanks again!

mark-piller · April 11, 2024, 4:38pm

What is the name of the table where the records created by users are stored?

James_Hereford · April 11, 2024, 6:06pm

The table name is “geoRef”. It is referenced by another table called “facility”. Facilities have addresses that have both the traditional fields expected for an address as well as a company-defined region “okrcRegion”. This region requires a lookup, so I figured I’d put all the information that can be looked up via zip code into a separate table. The table where new records are created and stored is “facility”. “geoRef” is static.

Generally I am still playing with things for now as a means of learning. Presumably in a long term version “facility” would drop all the related fields except the foreign key (zipcode / objectID) and I’d create a view that joined in the others.

facility.csv (50.6 KB)
geoRef.csv (6.0 MB)

mark-piller · April 11, 2024, 6:39pm

If a facility as a concept has a related geoRef record, you establish the relation when a user creates a new facility. To establish the relation, you need to know which geoRef record to use. If a zipcode identifies that geoRef record, then yes, you’ll ask the user what zipcode the facility should belong to.

James_Hereford · April 11, 2024, 4:45pm

The table name hypothetically is “geoRef”. It is referenced by another table called “facility”. Facilities have addresses that have both the traditional fields expected for an address as well as a company-defined region “okrcRegion”. This region requires a lookup, so I figured I’d put all the information that can be looked up via zip code into a separate table.

James_Hereford · April 11, 2024, 4:45pm

Sorry, to be clear. The table where new records are created and stored is “facility”. “geoRef” is static.

James_Hereford · April 11, 2024, 8:20pm

Understand. Sorry again - I forgot that I can name the column in the UI whatever I want. The user doesn’t need to know that the field name is objectID.