Overwrite Hosted Table in ArcGIS Online

2508
10
05-13-2019 08:25 AM
MatthewStull1
Occasional Contributor

Hi, I have a hosted table in ArcGIS Online (AGOL) that contains police arrest log data from the last 7 days.  This hosted table was published to AGOL from an Enterprise Geodatabase table on my local network using ArcGIS Pro.  What I need to do is overwrite this table every night so that the last seven days of arrest logs are showing.  I only need to update the table, there is spatial data involved.  Also, I need this hosted table to be able to be downloaded because it will be shared with the public on our City's ArcGIS Hub Open Data site.  


If overwriting it won't work, I was thinking of trying to delete it on AGOL and then republishing each night.  I need this to be automated process using a python script or maybe using Safe Software FME (which my City has a license of).  I have tried publishing this table, with an empty feature class, to our ArcGIS Server and registering the service in AGOL but that didn't work as it wouldn't allow for downloading in AGOL (the ArcGIS Server service was publicly available).  FYI, you can't publish a standalone table as a feature service with ArcGIS Server, that's the reason I included the empty feature class (spatial layer).

Any ideas?  Thank you!

0 Kudos
10 Replies
MatthewStull1
Occasional Contributor

I figured out this issue using FME.  I deleted my existing AGOL hosted table and service definition layers/items.  Then I reran my FME script/model and gave the FME writer a new name for the output table (one that didn't exist in AGOL, nor did I pick one of the existing tables in AGOL, which I had done before).  It ran perfectly!  FME created a new hosted table AND a file geodatabaes in AGOL; both with the same name.  It appears that the file geodatabase is the key to getting this update working, at least with FME (I didn't have a file geodatabase before).  This probably holds true even if you are trying to do this kind of an update using python but I haven't tried that.  

0 Kudos
Daniel_Hardwick
New Contributor III

Hi Matt,

Bit of a thread revival here but I am hoping you get to see this as it sounds like you were doing something similar to what we are trying to do within my organisation. 

Basically we have a user who wants to use Dashboard to display a variety of data, and the majority of it is table data. We want some sort of process so the updating of data is automatic, or as close to automatic as possible. 

Our setup which works well is out spatial is data stored in SQL server, brought into ArcGIS Pro, published as a service (via ArcGIS Server) to Portal/AGOL. Then any updates in ArcGIS Pro/SQL is reflected in Portal, or vice versa. But with this being table data, the same rules don't seem to apply... I tested uploading a table into our SQL database and then brought this into ArcGIS Pro and published to Portal. But when publishing the table into Portal it appears to be a standalone copy of the table and is not linked. If I make a change in the table in Pro it doesn't update in Portal, and vice versa. 

But the likelihood of us updating the data in this way is not very likely, but this was just to test that the two were linked, but they aren't. I was interested to see you published the table with a blank feature class and tried it that way... I am not sure how you managed to do that, as even if I have a geodatabase with both the table and empty feature class within, I can only seem to upload one item?

Anyway the way we probably want to do it is every week just drop into Portal/AGOL an updated version of the file, then dashboard/any web maps will pick up the new data and display this. You said you used FME to do this and we have just bought FME, is this still working well and was easy enough to setup and most importantly updates the file every week or whatever time period you specified? 

Any help is appreciated.

Thanks

0 Kudos
MatthewStull1
Occasional Contributor

Hi Daniel,

Thank you for picking us this thread again.  Here is some more information on what I have found and what I do with FME:

I do NOT recommend publishing a standalone table with ArcGIS Server.  First of all, as I said in the first post, you can't publish a table to ArcGIS Server by itself; it has to have a spatial layer (feature class) with it.  That didn't work out for me so I switched over and used the FME software.

At my office, we have the desktop and server versions of FME.  We created an FME script/workspace that just pushes our data from a table in SQL Server Geodatabase (SDE) directly to a table in ArcGIS Online.  In this process, FME truncates (it's not deleted) the table in ArcGIS Online and then rewrites the whole table from SQL Server to it.  If you do this, make sure you don't create anything in ArcGIS Online first.  FME Server allows you to run this job at a scheduled time.  FME Server is also set up to send me an email each night to say whether the job ran successfully or not.  I have about a dozen jobs that run on FME Server each night.  If you don't have FME Server, you can still have FME run jobs with the desktop version.  We have someone here at my agency that does this.  I believe they just use Windows Scheduler to do it.

So in summary, use FME, it's WAY easier to manage than trying to do it with the ESRI tools.

Anyway, if you want to see any samples that I have, just let me know and I will send them over.

Matt

Daniel_Hardwick
New Contributor III

Thanks Matt, I didn't expect a reply so soon or possibly not at all with it being an old thread! 

We also have FME desktop and server, although I have only used desktop as usually translate data, I do have a task on a schedule with windows scheduler but I would probably do this in Server as this is what it is there for really, and I guess I could even configure it to run whenever it see's a new file in the directory. 

Anyway, so the way to do it in FME is create a reader to pick up the table (I guess this could just be within a folder on one of our servers, doesn't need to be SQL), and publish it to AGOL (Portal in our case), then run at a schedule of choice? 

Couple of questions - I have not actually used FME to connect to Portal or AGOL, I presume this is easy enough to connect to (I will google)?

Did you do this work in 2 stages, the first one take the SDE and create the table in AGOL, then create a second work bench which reads in table/SDE again but this time it overwrites/truncates the existing table? Or did you manage to do it all in one workbench? 

Thanks again

Dan

0 Kudos
MatthewStull1
Occasional Contributor

Hi Daniel,

Connecting to AGOL is easy.  When you create the Writer in FME, you will pick the AGOL Writer.  It should help you create the connection file to AGOL when you put in your AGOL username and password.  Please note that if you are not using at least version 2019.2 that the AGOL connection will timeout after several months and you'll have to reset it.

The process I had does it in one workspace/script.  It's really simple, the Reader reads the data and then just pushes it over to the Writer.  You set the truncate setting in the Writer itself.  That's it.

Definitely use FME Server, it makes it very easy to manage and schedule jobs and get the email notifications.

Matt

Daniel_Hardwick
New Contributor III

Thanks Matt

That does sound really straight forward... would you mind sharing some samples/screenshots? 

Thanks

Dan

0 Kudos
Daniel_Hardwick
New Contributor III

Hi Matt, 

I have just spent an hour or so trying to connect to our Portal within FME and I have had no luck. I wanted to run past you what I tried to check it looks correct, as it could be a firewall issue or something permissions related. 

I firstly tried to just add a reader and then a writer to ArcGIS Portal. When setting the parameters I set the Portal URL to our URL, left the authentication to 'Generate token' and entered my user and password in the format as it appears in Portal (username@domain). I then attempted to press the ellipses on 'feature service' and it failed trying to retrieve feature types, the error mentions 'could not connect to server... check connected to internet and that no firewall is blocking outbound connections'. 

I tried other authentication types, as I am not sure which one I should use, but we have our portal setup with our Active Directory/Single sign on.

This led me to googling connection Portal to FME and found an article saying you should add a portal connection via the 'manage web services' options within FME. So I also tried this following their guidelines, but this failed. I can go into the details of what I did within here, but I first wanted to check how you connected to AGOL, did you just do it in the writer or did you first configure it in 'manage web services'? 

Thanks

Dan

0 Kudos
MatthewStull1
Occasional Contributor

Hi Dan,

I don't think I can help you with the Portal connection.  You'll have to contact FME support if you can't get it to work.  We don't use Portal, we just use regular AGOL.  I do agree with you that it's a permissions issue.  I think that once you get that worked out it should work for you.  I have attached a screenshot of what mine looks like (using FME Desktop version 2018).

Sample of FME Script to push table data from SDE to AGOL

Matt

0 Kudos
Daniel_Hardwick
New Contributor III

Thanks Matt,

So just to confirm, you don't go into manage web services, you just setup your connection directly within the writer?

I have just tried to set my writer up to read our AGOL rather than Portal, and it fails to featch feature layers so I think it could be a permission thing, will speak with our infrastructure team I think.

Thanks again

Dan

0 Kudos