Skip to main content

How to Create a Data Source for data on an sFTP Parquet File

Updated

If you have an sFTP server with data you'd like to import into Openprise, please use these instructions as a guide to creating your data source.  The files on the sFTP server should be constructed in parquet format.

Note: If you want to create multiple data sources with different data formats (ie. a file for leads and one for accounts), you must place the files in separate folders on your sFTP server.

Set up your FTP Server

To begin, open or download a separate program to access the server. In the instructions below, we're using FileZilla, which can be downloaded from the internet. 

  1. Open your sFTP Software program
  2. Enter the credentials to access the file server.
    1. You will need credential attributes for: Host, Username, Password, Port.
    2. Input your credential attributes.
    3. Click Quickconnect to save credentials in FileZilla.1.png
    4. Once connected, create a new directory by right-clicking on the top level folder and completing the details. This will be the directory where your file will be placed. In the screenshots below, this new folder is named "Ref - Country Lookup". 
      2.png
      mceclip0.png
      FAQ_04.png
       
  3. Once the folder is created, drag and drop your data file from your desktop to the newly created folder[Ref - Country Lookup].
    mceclip2.png

Create the data source in Openprise

 Go to Data > Data > Click Add Data Sources > select Standard Connector from the dropdown
ftp_csv2.png

Name your Data Source and select FTP - Parquet Files as the Source Technology and Data Format. 

ftp_parquet1.png

Click the Add account information button to connect your sFTP to Openprise. 

ftp_parquet2.png

ftp_parquet3.png

  1. Add account information: click this button to authenticate the data source using your sFTP credentials
  2. Add account: Click this option when authenticating the data source for the first time, or when using a another sFTP account to authenticate the data source
  3. Select from an existing account to authenticate the data source

If you select the Add Account button, you will need to include the following credentials from sFTP. 

ftp_parquet4.png
  1. File Protocol: select one of the three file protocol options
    • SFTP (Port 22)
    • FTPS (Port 990)
    • SFTP (Port 22)
  2. Select a credential type from the drop down. Options include:
    • Password
    • Certificate
  3. Host Name
  4. Username
  5. Port
  6. Password
  7. Root folder (e.g. "/") 

Once you have entered your credentials, click Save. You will then be navigated back to the create Data Source window.
ftp_parquet5.png

  1. Pick the directory or entity that contains your data file.
  2. Decide how files should be imported by filling one or more of the checkboxes below. The default option will be "import fields by name". Then click Next in the top right corner.

The file will be located on the server and opened and read. The Parse screen shows the first 50 records in the file. Check to make sure Openprise is reading the correct file and is reading the data appropriately.
FAQ_06.png

Click "Continue" in the top right corner to move onto the Map screen.   
FAQ_07.png

In the Attributes Configuration table, map the fields with the correct data Type. When finished mapping, choose a Primary timestamp attribute and click Finish in the top right corner. 

Next, import your file

Click on Import now from the Data Sources page to import the records from your file on the sFTP server into your Openprise data source. 

parquet8.png

Once completed, use the data source in the same manner as any other data source.

Related articles