Data Relation issue

Data Service
1

We have uploaded data from our previous database and have our static data loaded properly. The only thing left is that our data does not have the relational columns set. The data contains a few thousand items so manually setting relations is not an option. We wrote a script to set these relations but are getting an error about our concurrent request limit being 100. Therefore we are not able to loop through the entirety of our data and set the relations. The data already has a static column specifying which object it is related to in the other table.

Is there an easier way than running a script to set the relations? We just need the relational column to show the object which is specified in the static column ID. Could you guys do this on your end?

APP ID: 9A13BF3D-3E41-C30E-FF1B-94129ABED000

1 Like
2

Following as well.

3

Are the relations set in bulk? For instance one parent with multiple child objects? Or each relation is configured separately?

Btw, when importing data with CSVs, you can define the relations at that level and Backendless will “wire” the related objects accordingly.

1 Like
4

The relations are being set for one parent object at a time. All relations are one-to-one relationships. Some parent objects have two relational data columns. The relations are being set in bulk in the sense that we were trying to get all of the relations set with one cloud code call, which would retrieve 25 objects at a time, and loop through those 25 objects one at a time to set the relations, then continue grabbing 25 objects until all objects have values for the relation columns.

I hope that answers your first line of questions but let me know if you need clarification.

Also, we are definitely open to setting the relations with a CSV upload, as we did so to upload all of our static data. Could you point to some docs or examples of people setting relations with a CSV upload? Or show up what kind of values create the relation?

EDIT: I found a well-written article you wrote regarding the CSV Relational Upload:

5

The article above gives us a good idea of how to do the upload with relations but doesn’t seem to provide a solution to our particular challenge.

Can you set the relation with anything other than the objectId?

We are uploading data from our previous provider. Therefore the data does not have an objectId. It does have a unique identifying column that is auto-incremented. An example is that every ‘event’ object has an ‘eventId’.

Your article shows you creating a relationship in the customer’s table that relates to an address object. To do this, you place the related address’s objectId in the intended column for each line of data in customers.csv. Could you do the operation with a different identifying column such as ‘eventId’?

6

Example

Here is our data with a couple of columns removed for simplicity:

// users.csv
"userId({""type"":""AUTO_INCREMENT""})",firstName,lastName,birthdate,lastChurchId
1,Jane,Doe,,0
2,Lauren,Crown,3/11/1992,10
4,John,Doe,9/3/2020,8
6,Test Contact,Church,,1
8,Test Contact,Again,,1
9,Here I Am,Admin,4/1/2020,10
10,Thomas,York,5/1/1970,3

We want to add a relation column to the settings table below connecting to a user.

// settings.csv
"settingId({""type"":""AUTO_INCREMENT""})", hideBirthdayYear, textMsgs, userId,
1,1,1,2,
3,0,1,4,
4,0,1,6,
5,0,1,9,
6,0,1,10,

As you can see, the settings.csv has a column reserved for ‘userId’. That column’s value identifies which user the settings are related to. The users.csv has a userId which is a unique auto-incremented value used to identify each object. A quick example would be that the first row of data in settings.csv has a ‘userId’ of 2. This means that the settings are for Lauren Crown’s account. This use of primary keys is how our previous backend provider handled relations and is present in all our CSV data.

Given the above data, How would we go about uploading settings.csv so it will have a related column linked to the corresponding user object?

If there is no way of doing this, how would you go about uploading data such as ours so that it had relational columns?

1 Like
7

Hi, @Noah_Caldwell

Can you set the relation with anything other than the objectId?

No, relations can only be set up with the objectId column. So in your case, if you have a relationship, you need to create an objectId for each record and set a relationship between them.

Given the above data, How would we go about uploading settings.csv so it will have a related column linked to the corresponding user object?

If there is no way of doing this, how would you go about uploading data such as ours so that it had relational columns?

I suggest in your case to create an application (in whatever language you are most comfortable with) that you can run via your csv.

You can see the structure of the correct csv files by exporting some relationship tables from Backendless.

Regards,
Marina

8

Hey Marina,

We are in the process of following the steps you outlined above but have run into an issue when uploading Users data. We have a CSV file of the user’s data with the objectId relations set but both times we have attempted to upload it, 0 records are inserted.

I exported the CSV of the blank user’s table to ensure that all of the columns match, and it looks like the two relations tables that we created are not being recognized.

Please let me know what I can do to resolve this issue.

Thanks,
Will

9

On second look… the relations are being recognized, but when I attempt to upload the file none of the data is recognized. I copy and pasted the log I receive below.

1 15:46:00 IMPORT_DATA Importing Started.
2 15:46:00 IMPORT_DATA Preparing files to import
3 15:46:00 IMPORT_DATA Creating temporary tables
4 15:46:00 IMPORT_DATA Creating user’s tables
5 15:46:00 IMPORT_DATA Checking for existing views with the same names as in imported tables
6 15:46:00 IMPORT_DATA Checking denied, duplicate and dataSize for columns.
7 15:46:00 IMPORT_DATA Preparing Users table: initializing columns.
8 15:46:03 IMPORT_DATA Preparing Users table: updating column related IDs.
9 15:46:03 IMPORT_DATA Preparing Users table: initializing relations.
10 15:46:03 IMPORT_DATA Resolving related table.
11 15:46:03 IMPORT_DATA Getting meta-info about insetable tables.
12 15:46:03 IMPORT_DATA Start import table: Users
13 15:46:03 IMPORT_DATA Started import data from csv Users.csv
14 15:46:03 IMPORT_DATA Will perform Inserting or ignore (on duplicate by objectId).
15 15:46:03 IMPORT_DATA Reading next portion (0) of data from csv Users.csv.
16 15:46:03 IMPORT_DATA Created new import subtask (1) for Users.csv with 1500 elements.
17 15:46:03 IMPORT_DATA Reading next portion (1) of data from csv Users.csv.
18 15:46:03 IMPORT_DATA Started import subtask (1) for Users table.
19 15:46:03 IMPORT_DATA Created new import subtask (2) for Users.csv with 1500 elements.
20 15:46:03 IMPORT_DATA Reading next portion (2) of data from csv Users.csv.
21 15:46:03 IMPORT_DATA Started import subtask (2) for Users table.
22 15:46:03 IMPORT_DATA Created new import subtask (3) for Users.csv with 930 elements.
23 15:46:03 IMPORT_DATA Reading next portion (3) of data from csv Users.csv.
24 15:46:03 IMPORT_DATA Started import subtask (3) for Users table.
25 15:46:04 IMPORT_DATA Table ‘Users’ was imported at 1 seconds. Saved 0 records.
26 15:46:04 IMPORT_DATA Finish import table: Users
27 15:46:04 IMPORT_DATA Started restoring relations.
28 15:46:04 IMPORT_DATA Counting relations.
29 15:46:04 IMPORT_DATA Checking for existing tables with the same names as in imported views
30 15:46:04 IMPORT_DATA Clearing user data table cache.
31 15:46:05 IMPORT_DATA Deleting temporary tables.
32 15:46:05 IMPORT_DATA Finishing pointers export.
33 15:46:05 IMPORT_DATA Start import pointers for table: Users
34 15:46:05 IMPORT_DATA Finished import of table: Users
35 15:46:05 IMPORT_DATA Restoring expiration tasks for DeviceRegistrations.
36 15:46:05 IMPORT_DATA Import finished.
10

Hello @Will_York!

Could you please provide your AppId?

Regards,
Alexander

11

The App Id is -
APP ID: 9A13BF3D-3E41-C30E-FF1B-94129ABED000

1 Like
12

Could you please provide the csv file you are trying to import?
(Please send it in a private message in Slack)

Regards,
Alexander

13

Just sent it over.

14

Could you please tell me if you received an email confirming that the import was finished?

Regards,
Alexander

15

I think the problem is that you are trying to import a table that has relations with other tables.
Try adding all the necessary table files and importing them together.

Regards,
Alexander

16

I did not receive an email that the import was finished.

The users table has two relations - addresses and Churches and both of these tables are already uploaded into our dataset.

17

I uploaded addresses yesterday, then exported the file to get the Backendless created objectId. I then added those objectId’s to the relation in the Churches csv and uploaded that file. The relations are showing up correctly on the Churches table within Backendless. I then exported both the Churches and Addresses tables so that I could associate the correct ObjectIds in the user table. The file I Slacked to has the correct Object Id’s inserted for both Addresses and Churches within the User’s table.

18

I figured out what the issue was. The error was in the csv file, I have written to you in Slack exactly where it is.

Regards,
Alexander

19

I removed that single line of data and it looks like the upload worked. Weird that one single line of data was causing the whole issue! Thanks for the help as always.

1 Like
20

On second glance, only 1500 of the 3930 Rows were uploaded. It looks like it might be due to a ‘null’ record in our birthdate column. Will test and respond back.