I have a few hundred thousand flat text files which contain a single column of values. The first 40 values are parameters followed by between 4000 - 8000 rows of sensor data. The files are imported once, will never change and will be accessed by parameter selection for further analysis/calculations.

I think using two tables (one for parameters, one for sensor data) would be a good schema. So I need to split the file after import, transpose the parameter rows to columns and then load all the sensor data into a single table with a key back to the row in the parameters table.

I looked at pivot and unpivot but that won't let me transpose a single row to columns.

Any help on how to do this in SSIS or SQL and thoughts on my schema appreciated I'm not sure if the database will be responsive when I get a few million rows in the sensor data table.

  • 2
    Can you provide a sample data, the definition of the tables you are going to use? – user507 May 23 '15 at 0:12

using ssis and some c# / vba

  • add a foreach loop to iterate over the files
  • create a variable to use for the key (increment on each iteration)
  • in the loop add a dataflow consisting of
    • a script transform (source) to read first 40 lines from the file and pivot
    • a derived column to add the key (from var)
    • a destination to the table
  • in the loop add a dataflow consisting of
    • a script transform (source) skipping the first 40 lines and reading the rest
    • a derived column to add the key (from var)
    • a destination to the table
  • [optional] delete or move the file with a file task

the code for pivoting would read the first 40 lines into an array then just set the output columns mapping to the array's index.

consideration may need to be given to insure the key remains unique on subsequent runs and may need initialising to the max(key) + 1 from exiting data, use an sql task for this.

| improve this answer | |

To be able to PIVOT values into the shape of your target Parameter table you need a set of (parameter_name, parameter_value) tuples. I assume the design is able to distinguish one parameter value from another, either because of its position in the file (row 1 is Parameter A etc.) or due to a label in the row (ParamA=ValueA etc.).

For a positional design, create a reference table with two columns - row_number and parameter_name and populate it accordingly. Create a staging table with an identity column and parameter_value. Import the parameter rows from a single source file into this staging table. Values will be generated by the identity in the order that rows appear in the source file so the first parameter value will be ID=1 and the last will be ID=40. Join the staging and reference tables to get the tuple.

For the embedded labels use string functions to separate the name and value on the delimiter. This will be simpler with two staging tables - one with a single column to hold the raw records from file and the second with two columns to hold parameter_name and parameter_value separately.

I'd suggest you import each file in two steps. In step 1 bring in the 40 rows that represent your parameters. In step 2 bring in the remaining rows which are data. BULK INSERT has FIRSTROW and LASTROW parameters which can control this. bcp utility has similar switches.

Now you have a set of 40 (parameter_name, parameter_value) tuples that can be PIVOTed into your table's schema. Use this to INSERT to Parameter table, which presumably will have a surrogate primary key which you can capture (an INSERT .. OUPUT will help). Use this value in the BULK INSERT of the remaining data points. This is why I suggest doing it in two steps.

Package the whole thing as a stored procedure. Use a Powershell script to itterate over your files and call the SP for each. You will need to use dynamic SQL for the BULK INSERT. If you use temporary tables for the staging table you can run any number of processing streams in parallel without worrying about tidy-up.

Before each file is processed TRUNCATE the staging table. This will reset the idenity as well as remove the previous file's values.

Of course the whole file could be read into a staging table and the parameters separated from sensor data afterwards. This works equally well, it just represents additional run-time work for little value in my opinion.

| improve this answer | |

From your question you're looking a billion sensor readings, plus or minus. Using an int for the foreign key to the parameters, and for the data itself, and another to separate each sensor reading from the others in a single file, that works out at just under 12GB of sensor data. All up the DB's going to be quite manageable by modern standards. You don't mention it in your question but there has to be something to distinguish all those sensor reading from each other, even it if is only an implicit fifteen second (say) interval between each. In an RDBMS this will have to be made explicit as there is no inherent ordering.

I've run several systems with tables this large. They are not problematic per se. I think your use pattern will give you problems with this design, however.

Single row queries will be fine. The index is deep and takes some looking after - rebuilding and statistics and so forth, but an index lookup is an index lookup and it performs well. If you're returning a complete file's worth of data that will be OK, too. An index look up on the foreign key to the parameter table and a range scan to get the other few-thousand rows. No problem there.

It seems what you want to do is find all those "files" where, say, Parameter A = "X" and Parameter B = "Y" and retrieve the corresponding sensor readings. There is likely to be many parameter rows to match. With no a priori way to know what relationship these data have to each other on disk the optimzer will revert to table scans, and that will be slow.

I think you will be best served by throwing disk at the problem. Implement a data warehoure-style star or snowflake schema. Make each of your parameters a dimension. Your sensor data will be the fact table. Put a columnstore index on it. Make it clustered if your version and edition allow. This is a good fit since your data is immutable once loaded. Depending on your queries you may get value from adding Analytical Services cubes, too.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.