As announced by Google on December 11, 2018, Google Fusion Tables and the Fusion Tables API will be turned off on December 3, 2019. Fusion Tables support will be removed from FME after the service has been shut down. For ways to migrate your data out of Google Fusion Tables using FME, see the blog post 7+ Google Fusion Tables Alternatives.

Please note that this article will not be updated. To use these workspaces after Google Fusion Tables no longer works, change the readers/writers to Google Sheets or a similar product.

Introduction

Note: This tutorial requires a Google account and Microsoft Excel.

Using a standard Directory Watch configuration, FME Server will trigger a notification every time a watched file (or folder) is modified. This can create an overload of processing on the server. This tutorial presents a method to optimally handle these migrations using an idle time delay.

In the following example, an excel spreadsheet is being stored in a watched folder. When the spreadsheet is modified, a notification is generated by FME Server and the contents (i.e. filename, timestamp) are stored in a database table. The table is checked by a workspace running on a schedule and when a desired amount of time has elapsed for each modified file, the data is automatically backed up to a Google Fusion Table in the cloud.

Downloads

directory-watch-idle-delay.zip

Part 1: Watch a File Directory

1. Create the directory to watch

Navigate to the file system on the machine where FME Server is installed, or your own machine provided that FME Server is located on the same network. Create a new folder called ‘Resource’ and copy the Project_Inventory.xlsx file from the attached download into the new folder. Right click anywhere in the Resource folder and click Properties. In the Sharing tab click, Advanced Sharing then enable Share This Folder, ensure the permissions are set to write, then click ok. Copy the UNC Network Path (e.g. \\KC-LS-WINDOWS\Resource), we will need this in the next step and close the Properties window. Additionally, depending on your sharing settings within your computer, you might have to click the Share... button on the Resource Properties dialog and click Share.

Create a shared folder containing Project Inventory.xlsx, ensure permissions are set to full

2. Create the Directory Watch publication

Log into your FME Server and on the side menu bar under Notifications, select Publications. Click New, and give your publication the name 'sync_tool’ and add a new topic to publish to named 'sync_tool'. Select Directory Watch protocol and for Directory to Watch choose 'Specify a location', and paste the UNC Network Path we copied in the last step. Monitor MODIFY operations (remove CREATE and DELETE). Finally, set the Poll Interval to 1 Minute, you can leave it as the default, but for testing purposes a lower interval will be quicker to debug. Click OK to create the publication.

Create the new Directory Watch publication in FME Server called sync_tool, set it to watch your Resource directory.

3. Setup the Data Migration Workspace

Open the project_inventory_to_map.fmw (Workspace #1) in FME Workbench. This workspace writes Excel data to a Google Fusion table stored in the cloud. For more information on Google Fusion Tables refer to the user documentation.

In the User Parameters in the Navigator pane, under Published Parameters, double click on the [SourceDataset_XLSXR], set the path to "\\YourUNCPath\Resource\Project Inventory.xlsx", make sure you include the quotations.

Double-click on the SQLExecutor to modify the parameters. We will need to login to our personal Google account and authorize FME. To do this, select Parameters in the SQLExecutor, in the Google Fusion Table Spatial Parameters dialog box, click on the (...). In the OAuth authentication window, log in using your own Google account credentials to manage Fusion Tables and click Allow. You now have a Refresh Token in the Google Fusion Tables Spatial Parameters dialog, copy this token to avoid re-authentication in later steps. Click OK to exit the SQLExecuter parameters

Double-click on the [GoogleToken] User Parameter and paste in the Fusion Table Refresh Token to connect to your own account.

project_inventory_to_map.fmw (workspace #1) workflow

Publish to FME Server, create a new repository called Project_Inventory, then register with the Job Submitter Service. (Note: Ensure that you uncheck Upload Data Files so that the Excel file is not uploaded to the Server)

4. Setup the Directory Watch Receiver Workspace

In FME Workbench open project_inventory_status.fmw (Workspace #2), this workspace runs when a Modify trigger is sent by the Directory Watch protocol. Details from the notification message (i.e. filename, timestamp) are stored in a database, which in this case will be a Google Fusion Table.

This workspace was set up with a User Parameter called GoogleToken to speed up the process of authentication in the SQLExecutor and the Writer, it is the same process as the last step. Click on the GoogleToken Published Parameter and paste your Refresh Token.

Publish the workspace FME Server, in the Project_Inventory repository, and register the workspace using the Notification Service. Click Edit and choose sync_tool for Subscribe to Topic. (Note: You may receive a warning message - this workspace uses custom formats, which will need to be provided to FME Server - click yes to accept.)

project_inventory_status.fmw (workspace #2) workflow

In order for the workspace to run with a custom transformer on FME Server, we will need to upload the custom transformer. In FME Server, go to Resources and navigate to the Formats folder within the Engine folder. Click Upload and browse to the DIRECTORY_WATCH_READER.fds, which is located in the folder that was downloaded at the beginning of this tutorial.

Upload the DIRECTORY_WATCH_READER.fds to the Formats folder in FME Server

5. Setup the Directory Watch Receiver Workspace

Back in FME Workbench open project_inventory_sync.fmw (Workspace #3), this workspace is used to check the database(Google Fusion Table) for modified files and send them to be processed when necessary.

In the User Parameters, double click on [GoogleToken] and paste your refresh token. In the FMEServerJobSubmitter connect to your FME Server. Then select the Project_Inventory repository and select the project_inventory_to_map.fmw workspace, click the refresh icon in the lower left hand corner, to bring in the parameters from project_inventory_to_map.fmw. This will enable the project_inventory_to_map.fmw (Workspace #1) to run once project_inventory_sync.fmw (Workspace #3) has run.

Publish to FME Server in the same repository and register with the Job Submitter.

project_inventory_sync.fmw (workspace #3) workflow

6. Create the Fusion Tables

In FME Workbench open DB_Loader.fmw (Workspace #4), this workspace creates two tables in your Google Fusion Database.

In the Navigator pane under User Parameters, double click on [SourceDataset_XLSXR] and browse to the shared Resource folder and add the Project Inventory.xlsx file

Next, double click on [SourceDataset_CSV] and browse to the CSV file called project_inventory_file_status.csv that came with the file download.

Then update the [GoogleToken] just as before, then run the workspace. After running your translation, check in your Google Drive account to ensure the workspace ran correctly, you should see two files, Project Inventory and project_inventory_file_status.

DB_loader.fmw (workspace #4) workflow. Creates two Google Fusion Tables

7. Setup a Schedule

The last step is to setup a schedule to check our tracking database. This can be set to run every few minutes. This schedule will run project_inventory_sync.fmw (Workspace #3), which checks the tracking database for the last modified time. If it is greater than the idle time set within the workspace, the data is migrated.

In FME Server, go to the Schedules page and click New to create a new schedule named project_inventory_sync and create a new Category called Project_Inventory. Check Run Immediately, then set it to Repeat Every 2 Minutes or however often you would like it to repeat.

Then set the Repository to Project_Inventory and select the project_inventory_sync.fmw (Workspace #3) and click OK to set up the schedule. A Published Parameters section will be exposed, you can adjust the Seconds to Wait if you like, this is the desired amount of time to wait after the Excel file has been updated and is idle. Once this condition is met, the data will be synced to the Google fusion table. Also, ensure that your Refresh Token is correct. Click OK to start the schedule.

Part 2: Verify the Workflow

In FME Server, click on Notifications, then click the Topic Monitoring Tab. For Topics to Monitor, add sync_tool. This will send a notification each time the file is modified. Ensure this page is left open for Topic Monitoring to continue.

At this point, all the necessary steps are in place to have an optimized backup system for your spreadsheet data. To test this try the following steps:

Open Google Drive and open the two Fusion Table files you created, Project Inventory and project_inventory_file_status. To do this, right click on the file, then Open With > Google Fusion Tables.

Open the Excel document in Excel (project_inventory.xlsx) and enter a new row:

100007 repair  manhole replaced 49.2755567 -123.0576200

Save the excel file, ensure it is saved to the shared Resource folder and the file extension is .xlsx. This will trigger the Directory Watch protocol to post a Modify to the sync_tool topic. If you open the Jobs up in a new FME Server window, the project_inventory_status.fmw workspace will have run, then depending on your schedule, the project_inventory_sync.fmw will run, depending on how the schedule lined up to your update, you might have to wait for the schedule to run twice in order for the Google Fusion Table to update. Wait up to five minutes for this process to run through depending on how your schedule was set up and the Seconds to Wait parameter was set to. After the allotted time has passed refresh both Google Fusion Tables to see the updates.

Topic Monitoring in FME Server when the Project Inventory.xlsx file is modified in Excel

Conclusion

Using Idle Time Delay is useful for keeping a backup of your documents while you are updating them. The workspace only runs when it detects a change after a certain period of time in your local document, this frees up valuable server engines, only running the workspace when required instead of on a schedule. Creating a separate status table is also useful for ensuring that the updates to the local file were expected, especially when the file is shared among several users.

Tutorial Overview: Directory Watch | Previous: Directory Watch Publisher | Current: Directory Watch Publisher with Idle Time Delay (Advanced)