# Workflows SFTP Data Connector Example

## Download the Sample Project

1. Download the sample project from the following URL


 [GitHub Gist](https://gist.github.com/wchen4/c15518885045cc9ebb9a45f6419fab1e/raw/1ad0498954b66fb361ffcf801349bc8fb9649b00/sftp_pull_push.tar.gz)

1. Extract the downloaded project. For example from the command line you could run:



```bash
tar xzvf sftp_pull_push.tar.gz
```

1. Navigate to the newly created workflow project directory. For example:



```bash
cd sftp_pull_push
```

## Upload Sample Import File to Your SFTP Server

Upload the *imports/sample_data.csv* file to your SFTP server. This file contains 22 records of sample data which we will use to query later.

## Modify Example to Match Your Environment

1. Modify the *pull_push.dig* file and update **result_url** by replacing values in **<...>** with the settings of your SFTP server for the export.



```yaml pull_push.dig
_export:
  td:
    database: workflow_temp

+import_from_sftp:
  td_load>: imports/sftp_load.yml
  table: pull_push_tutorial

+export_to_sftp:
  td>: queries/count_rows.sql
  result_url:
    {
      "type":"sftp",
      "host":"<host>",
      "port":<port>,
      "auth_method":"Password",
      "username":"<username>",
      "password":"<password>",
      "user_directory_is_root":true,
      "path_prefix":"export_example.csv",
      "formatter":{"type":"csv"}
    }
```

1. Modify the *sftp_load.yml* file with your SFTP connection details for the import.



```yaml sftp_load.yaml
in:
  type: sftp
  host: <host>
  port: <port>
  user: <username>
  password: <password>
```

## Creating the Required Tables

To run any data connector job in Treasure Data, you must have the database and table you’re loading data into created.

1. Open the TD Console.
2. Navigate to Data Workbench > Databases.
3. If **workflow_temp** doesn't exist, create a New Database and name it **workflow_temp**.
4. Create a new table and name it push_pull_tutorial.


![](/assets/pushpulltable.aa8aeea3578432b107c99ce42433e599bcbb7359c665a8cf945a45b339cc25de.2e4f387b.png)

Alternatively, you can use the following command from where you have the TD Toolbelt installed:


```bash
td table:create workflow_temp pull_push_tutorial
```

## Adding the Workflow file to Treasure Data

1. From the TD Console, navigate to Data Workbench > Workflows.
2. Add a new workflow and name it **workflow_sftp_pull_push**.
3. Select a blank template.
4. In the workflow definition, copy and paste the example files you modified above.
5. Save and commit the workflow.


Alternatively, to upload the queries and workflow files into Treasure Data:

1. From the machine where TD Toolbelt is installed.
2. Open a command window.
3. Navigate to the **sftp_pull_push** directory.
4. Type the following:



```bash
td wf push sftp_pull_push
```

The necessary files for the workflow are now registered and viewable from within the TD Console. In this example, the project name is **sftp_pull_push** and the workflow name is **pull_push**.

## Executing the Workflow on Treasure Data

1. From Data Workbench > Workflows > pull_push.
2. Select New Run.
3. Review the Run History to watch the execution steps of the workflow.


Alternatively, run the following command to execute the workflow already submitted to Treasure Data:


```bash
td wf start sftp_pull_push pull_push --session now
```

If the run was successful, you will see a file called *export_example.csv* in your SFTP server with the results of the query.