Dataro Agent
      Back to home
      1. Dataro Help Centre
      2. Integrating with Dataro
      3. Dataro Agent

      Dataro Agent Technical Details

      Export Formats

      Results can be exported as either zipped CSV files (using --format csv) or Parquet files with gzip compression (using --format parquet). N.b. CSV exports will be deprecated in future.

      Database Accounts

      • A database account (as opposed to Windows Authentication) is required for scheduled operation.
      • It is the client's responsibility to ensure that the database account has only select privileges on the required tables.

      Installation Instructions

      • Dataro will provide a zip file containing:
        • dataro_agent.exe
        • dataro_queries.sql
        • dataro_sftp.key
        • config.ini
      • Unzip these files into a directory accessible to the user who will own the scheduled tasks i.e. C:\dataro. We will call this the 'working directory'.
      • Edit the config.ini to include the database host, name, username and password. We suggest that you encrypt the database username and password using the method described below.
      • Open a command window and navigate to the working directory.
      • Run the following command to confirm that the agent is configured correctly:
      dataro_agent.exe --mode export --config_file config.ini --key_file dataro_sftp.key --query_file dataro_queries_test.sql --format csv --scheduled False
      • If the program completes without issue, then proceed to setting up for Scheduled Operation.

      Scheduled operation

      Typically the agent will be scheduled using Windows Task Scheduler.

      Export

      dataro_agent.exe --mode export --config_file config.ini --key_file dataro_sftp.key --query_file dataro_queries.sql --format csv --scheduled True
      Triggers
          On a schedule
          Daily
          Every 1 Day
          (Time is as per client's requirements)
      Actions
          Start a Program
          Program/Script: C:/dataro/dataro_agent.exe
          Add Arguments: --mode export --config_file config.ini --key_file dataro_sftp.key --query_file dataro_queries.sql --format csv --scheduled True
          Start in: C:/dataro/

      Import

      dataro_agent.exe --mode import --config_file config.ini --key_file dataro_sftp.key --scheduled True
      Triggers
          On a schedule
          Daily
          Every 1 Day
          (Time is as per client's requirements -- approximately. 3 hours after the export)
      Actions
          Start a Program
          Program/Script: C:/dataro/dataro_agent.exe
          Add Arguments: --mode import --config_file config.ini --key_file dataro_sftp.key --query_file dataro_queries.sql --format csv --scheduled True
          Start in: C:/dataro/

      Encrypting values in the .ini file

      We suggest that you encrypt data such as database password before storing in the ini file.

      The Agent has built-in functionality to encrypt strings and decrypt encrypted values in the ini file.

      The example below demonstrates how to encrypt a database password and save in the ini file.

      dataro_agent.exe --enc MySecretPassword123

      This will generate some logs in the console, look for one like 'This is the encrypted text':

      Copy the string starting from (and including) 'enc_' and paste this into the config file:

      Now, when you run the agent normally, it will identify an encrypted string by the 'enc_' starting characters and decrypt the value inside the program.

      CLI Arguments

      optional arguments:
        -h, --help            show this help message and exit
        --config_file [CONFIG_FILE]
                              Config file name
        --key_file [KEY_FILE]
                              SFTP Private Key File
        --query_file [QUERY_FILE]
                              Dataro SQL File
        --copy_only [COPY_ONLY]
                              Only do sftp copy
        --scheduled [SCHEDULED]
                              Don't require key return at the end.
        --mode [MODE]         Export or Import data
        --format [FORMAT]     csv or parquet data
        --db_update_script [DB_UPDATE_SCRIPT]
                              Filename of custom script to synchronise database
        --enc [ENC]           Encrypt a string for usage in ini files