title: How to schedule importing data files from SFTP server located on compute engine instance into BigQuery?
body: <h2>What I want to achieve:</h2>
  <p>Transfer hourly coming data files onto a SFTP file server located on a compute engine VM  from several different feeds into Bigquery with real-time updates effectively &amp; cost-efficiently.</p>
  <h2>Context:</h2>
  <ol>
  <li><p>The software I am trying to import data from is an old legacy software and does not support direct exports to cloud. So direct connection from software to cloud isn't an option.</p>
  </li>
  <li><p>It does however support exporting data to a SFTP server. Which is not available via any GCP tools directly.</p>
  </li>
  <li><p>So I have setup a manual SFTP server using <code>vsftpd</code> on a compute engine VM instance with expandable storage then giving it a static IP and hardwired that IP into my software. Data now comes to the compute engine instance on hourly intervals seamlessly.</p>
  </li>
  <li><p>Files are generated on hourly basis. Thus a different file for each hour. However they might contain some duplication .i.e. some of the end records of previous hour's file may overlap with the beginning of the current hour's file.</p>
  </li>
  <li><p>Files are coming from different source feeds and I have feed names in the filenames so ever-growing data on my compute engine VM instance look like:</p>
  <pre><code>feed1_210301_0500.csv
  feed2_210301_0500.csv
  feed3_210301_0500.csv
  feed1_210301_0600.csv
  feed2_210301_0600.csv
  feed3_210301_0600.csv
  feed1_210301_0700.csv
  feed2_210301_0700.csv
  feed3_210301_0700.csv
  ...
  </code></pre>
  </li>
  </ol>
  <h2>What I have tried:</h2>
  <ol>
  <li>I have set Bigquery access &amp; cloud storage permissions within VM instance to access data from VM instance onto BigQuery:</li>
  </ol>
  <p><a href="https://i.stack.imgur.com/Xarln.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Xarln.png" alt="enter image description here" /></a></p>
  <ol start="2">
  <li><p>I have tried importing data into BigQuery directly as well as on google cloud storage to import data from there and yet there is no option to directly import data from VM instance to BigQuery nor I can somehow import data from VM to GCS then to load into BigQuery but there is no option for that and documentation is silent on the matter of scheduled transfers as well.</p>
  </li>
  <li><p>There are some external data transfer services like <a href="https://fivetran.com/connectors" rel="nofollow noreferrer">Fivetran</a> and <a href="https://docs.hevodata.com/sources/file-storage/ftp---sftp" rel="nofollow noreferrer">HevoData</a> but they are relatively expensive and also seem much of a overkill as both my source or destination is on the GCP and it wont be much different than having a third VM and scheduling some scripts for imports. (Which BTW is my current workaround :D i.e. Using python scripts to stream data into BigQuery as explained <a href="https://cloud.google.com/bigquery/streaming-data-into-bigquery#bigquery_table_insert_rows-python" rel="nofollow noreferrer">here</a>)</p>
  </li>
  <li><p>Currently I am exploring <a href="https://cloud.google.com/data-fusion" rel="nofollow noreferrer">DataFusion</a> which is only free for 120 hrs each month, has extra costs for underlying <a href="https://cloud.google.com/dataprep" rel="nofollow noreferrer">Dataprep</a> pipelines and not sure if its the right way to go about. Also I am currently exploring tools like <a href="https://cloud.google.com/scheduler" rel="nofollow noreferrer">Cloud Scheduler</a> &amp; <a href="https://cloud.google.com/composer" rel="nofollow noreferrer">Cloud Composer</a> to see if any fits my data needs but as of now could not find a viable solution.</p>
  </li>
  </ol>
  <p>I am happy to learn any new tools and technologies and any advice bettering the situation in anyway is also appreciated.</p>