IMD SolutionComputing LibraryShop main store

How-to

Send PI historian CSV exports to an S3 bucket hourly

Edge computing and IIoT Published 6 min read

Short answer

Keep the two jobs separate: the existing export produces the hourly CSV, and a scheduled uploader on a host that can read the export folder and reach the bucket delivers it to S3. Verify the completed CSV before any transfer, and scope the upload credentials to that one bucket prefix. The historian does not write to S3 by itself, and an SFTP endpoint is not a bucket.

On this page
  1. Split the work at the CSV file
  2. Check the export folder first
  3. A bucket is not an SFTP address
  4. Pick the host that bridges both sides
  5. Run the hourly upload
  6. Size the job before you schedule it
  7. When nobody is producing the CSV yet
  8. Questions asked

Two jobs hide in this requirement: producing the hourly CSV and delivering it to S3. Since the client already generates the files, leave extraction alone and add one scheduled uploader on a host that can both read the export folder and reach the bucket over HTTPS. Give that job an identity limited to writing one prefix, run it a few minutes after each export completes, and check each delivered object's byte count against the local file. The PI System does not push to S3 on its own, and an SFTP-style endpoint is not a bucket name, so settle which one the receiver actually gave you before configuring anything.

Split the work at the CSV file #

The PI Data Archive keeps time-series values in its own archive files, not in CSV and not in a relational database; a CSV on disk is an export somebody configured. If you need that distinction settled for the client's server, read where the PI Data Archive actually stores tag data.

Because the files already exist, the uploader never needs to query PI Points, hold a PI identity, or load an SDK. It needs read access to one folder and write access to one prefix. That is the decision: keep the boundary at the file. Wrong contents or missing intervals are fixed in the exporter; a completed file absent from the bucket is fixed in the uploader. Build a new extractor only when nobody is producing the dataset at all.

Check the export folder first #

Before any cloud configuration, read the newest file the way the uploader will: its name against the interval convention, its modification time against the export job's last success, its size, and its contents. A row count should equal points times samples per interval; gaps there are extraction faults, not transfer faults.

What you see Where the fault lives Next check
No file for the interval Export generation or its schedule Export job history and configured output folder
File still changing No completion signal defined How the exporter marks a file finished
Completed file, wrong interval or columns Extraction settings Point selection and time boundaries
Completed file missing from the bucket Transfer, credentials, destination Uploader log, bucket name, object key
Object present but partial Upload during the write Compare object bytes to local file bytes

A bucket is not an SFTP address #

S3 is object storage addressed by bucket, region, and object key; SFTP is an SSH session to a host. A bucket name will not type into an SFTP client, and a historian that writes CSVs does not become a file-transfer server because of it. Ask the receiver which they are giving you, and get the matching set: for a bucket, the name, the region, an object-key convention, and an identity allowed to write there; for an SFTP endpoint, the host, account, authentication method, and host key to verify. A bare bucket name with nothing else is an incomplete handoff, and the fix is one email, not a workaround.

If the receiving side turns out to be another PI System rather than a data lake, skip files entirely and use moving PI historian data to another PI server.

Pick the host that bridges both sides #

The transfer host needs exactly two things: read access to the export folder under the account the schedule will use, and an approved outbound HTTPS route to the bucket endpoint. Test both from that account, because an interactive test under your own login proves nothing about a background schedule.

The files can sit on the historian server or on another server; that is a deployment choice, not PI behavior. IEC 62443-3-2 models a plant as zones joined by conduits; in those terms the historian network is a zone, and the single authenticated, logged HTTPS path to the bucket is the conduit. Give the transfer host that conduit role instead of opening outbound internet access on the PI server. Where no existing machine qualifies, put the job on a dedicated fanless industrial mini PC and let it carry exactly that one path.

Run the hourly upload #

  1. Define the completed-file rule: the exporter writes to a temporary name and renames, or you accept files whose modification time has stopped advancing. Never upload on creation alone.
  2. Install an S3-capable command-line client on the transfer host and confirm HTTPS reachability to the endpoint from the scheduled account.
  3. Create a dedicated upload identity with permission to write under your one prefix and nothing broader. Do not reuse an administrative key.
  4. Push one completed file by hand under that account and confirm the command exits with status zero.
  5. Schedule it hourly, five minutes after the export's expected completion, with Task Scheduler on Windows or a cron entry on Linux.
  6. Log every run; alert on a non-zero exit and on a run that uploaded zero files.
  7. Verify the first delivered objects by size before calling it done.

An illustrative invocation, with invented folder, bucket, and prefix names:

aws s3 sync "D:/PIExports/hourly" "s3://example-plant-bucket/pi-hourly/"

A sync operation compares both sides and copies what is new or changed, so an hour that failed heals on the next run without a separate catch-up script. The test that proves a delivery: after the run, the object under your prefix reports the same byte count as the local file, 120,000 bytes for the example file sized below.

Size the job before you schedule it #

Illustrative numbers: 40 points exported as one-minute averages, one file per hour, at roughly 50 bytes per row of timestamp, tag, value, and quality.

  • Rows per file: 40 points × 60 samples = 2,400 rows
  • File size: 2,400 × 50 B = 120,000 B = 120,000 / 1,024 ≈ 117 KB
  • Per day: 24 × 117 KB = 2,808 KB ≈ 2.7 MB
  • Per 30-day month: 2,808 KB × 30 = 84,240 KB ≈ 82.3 MB
  • A full twelve months: 82.3 MB × 12 = 988 MB ≈ 0.97 GB

At that volume the decisions make themselves: hourly single-file sync with no compression or batching, a 30-day local buffer that costs about 82 MB on any host, and retry-on-next-hour as the failure policy, with a human paged only after two consecutive zero-file hours. Compression and batching start earning their complexity somewhere north of tens of megabytes per hour, not at 117 KB.

When nobody is producing the CSV yet #

First decide what the receiver needs: recorded events, evenly spaced values, or aggregates. Those are different datasets; an hourly aggregate file is not interchangeable with every event from the hour. For evenly spaced values, the scheduling and interval choices are the same ones covered in exporting historian trends to CSV at one-minute intervals.

Then pick one tool: the OSIsoft.PowerShell module for a scripted extractor, an AF SDK application in C# or VB.NET when the logic grows, PI Web API where the site already exposes and authorizes it, or DataLink and SMT for manual one-off pulls. For a first hourly dataset, run the PowerShell module on the transfer host and let one schedule produce then upload; note that this pulls the host into PI authentication mappings, which is exactly the coupling the file boundary avoided, so accept it for one small dataset and not for many.

When datasets multiply or must land in S3 continuously, PI Integrator for Business Analytics publishes PI System data to AWS targets that include Amazon S3, and the separate uploader becomes unnecessary for what it produces. For true streaming rather than hourly files, architectures combining AWS IoT Greengrass with PI Web Services exist; hourly CSV delivery does not need them.

Questions asked

Does the PI System create the hourly CSV files by itself?

No. The PI Data Archive stores values in its own archive files; a CSV sitting on a share is the output of something someone configured, such as DataLink, SMT, a scheduled script, or PI Integrator for Business Analytics. Find that job and its output folder before adding any cloud component to the chain.

What permissions should the upload account have?

Only the right to create and overwrite objects under the one prefix you publish to, over HTTPS. No bucket-wide administrative access, no credential shared with other systems, and keys rotated on a set schedule. If a scheduled run fails on permissions while your manual test worked, the job is running under a different account.

How do I confirm an hourly upload actually happened?

Two checks: the command exits with status zero, and the new object's reported size in bytes equals the local file's size. Then watch the zero-file condition. A run that uploaded nothing usually means the export never completed, not that the bucket rejected you. Alert after two consecutive zero-file hours before paging anyone.

When should the hourly upload run relative to the export?

Five minutes after the export's expected completion is enough when the exporter finishes predictably. If completion time drifts, stop trusting the clock and gate the upload on the completed-file rule, such as a rename into the final folder or a modification time that has stopped advancing, so you never upload a half-written CSV.

The client mentioned PI Integrator for Business Analytics. Do I still need an uploader?

Not for datasets that product already publishes. It can send PI System data to AWS targets that include Amazon S3, so point its output at your bucket and skip the separate transfer job. Confirm the installed version and licensing with the client; the uploader remains the fallback for files other tools simply drop in a folder.

Keep reading

  • How-to

    Backfilling trend history from a plant historian

    Yes, you can backfill trend history from a plant historian into a SCADA server. Extract the stored records through the historian's native export or ODBC interface, map each source point to its destination object with original timestamps and quality, insert the batches through the destination's documented history methods, and prove the import by reading the interval back from the destination. A saved CSV is a bridge, not proof of import.

    Edge computing and IIoT

  • How-to

    Export historian trend CSV at one-minute intervals

    Export the trend's recorded values and you get change-based events, about 17 minutes apart in this case, because the historian stores a value only when it changes. To get one row every minute, query the tag in the Query tool with regularly spaced retrieval at a 60-second interval and export that result; the grid comes from retrieval, not storage.

    Edge computing and IIoT

  • How-to

    How to move Wonderware Historian data into Canary Historian

    Yes. Backfill Wonderware history by exporting CSV files or a SQL database that Canary consumes, run a parallel real-time collection path for the same tags, and verify record counts and values at the timestamp where imported history meets live data.

    Edge computing and IIoT