Import-DbaParquet

Synopsis

Imports Parquet files into SQL Server tables using high-performance bulk copy operations.

Description

Import-DbaParquet uses .NET’s SqlBulkCopy class to efficiently load Parquet data into SQL Server tables, handling files of any size from small datasets to multi-gigabyte imports. The function wraps the entire operation in a transaction, so any failure or interruption rolls back all changes automatically.

Parquet files are read using Parquet.NET, which provides high-performance columnar data access. Unlike CSV, Parquet files contain schema information including column names and data types, which are used automatically during import.

When the target table doesn’t exist, you can use -AutoCreateTable to create it on the fly with string columns using UTF-8 varchar(MAX) by default (or nvarchar(MAX) with -NoUtf8). For production use, create your table first with proper data types and constraints. The function intelligently maps Parquet columns to table columns by name, with fallback to ordinal position when needed.

Column mapping lets you import specific columns or rename them during import, while schema detection can automatically place data in the correct schema based on filename patterns.

Perfect for ETL processes, data migrations, or loading reference data where you need reliable, fast imports with proper error handling and transaction safety.

Syntax

Import-DbaParquet
    [[-Path] <Object[]>]
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [-Database] <String>
    [[-Table] <String>]
    [[-Schema] <String>]
    [-Truncate]
    [[-BatchSize] <Int32>]
    [[-NotifyAfter] <Int32>]
    [-TableLock]
    [-CheckConstraints]
    [-FireTriggers]
    [-KeepIdentity]
    [[-Column] <String[]>]
    [[-ColumnMap] <Hashtable>]
    [-KeepOrdinalOrder]
    [-AutoCreateTable]
    [-NoUtf8]
    [-NoColumnOptimize]
    [-NoProgress]
    [-UseFileNameForSchema]
    [-NoTransaction]
    [[-StaticColumns] <Hashtable>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example 1: Imports the entire housing.parquet to the SQL "markets" database on a SQL Server named sql001

PS C:\> Import-DbaParquet -Path C:\temp\housing.parquet -SqlInstance sql001 -Database markets

Imports the entire housing.parquet to the SQL “markets” database on a SQL Server named sql001.
Since a table name was not specified, the table name is automatically determined from filename as “housing”.

Example 2: Imports every Parquet file in the \\FileServer\parquets path into both sql001 and sql002's tempdb database

PS C:\> Get-ChildItem -Path \\FileServer\parquets -Filter *.parquet | Import-DbaParquet -SqlInstance sql001, sql002 -Database tempdb -AutoCreateTable

Imports every Parquet file in the \FileServer\parquets path into both sql001 and sql002’s tempdb database. Each Parquet file will be imported into an automatically determined table name.

Example 3: Shows what would happen if the command were to be executed

PS C:\> Get-ChildItem -Path \\FileServer\parquets -Filter *.parquet | Import-DbaParquet -SqlInstance sql001, sql002 -Database tempdb -AutoCreateTable -WhatIf

Example 4: Import only Name, Address and Mobile even if other columns exist

PS C:\> Import-DbaParquet -Path c:\temp\dataset.parquet -SqlInstance sql2016 -Database tempdb -Column Name, Address, Mobile

Import only Name, Address and Mobile even if other columns exist. All other columns are ignored and therefore null or default values.

Example 5: Will import the contents of C:\temp\schema.data.parquet to table 'data' in schema 'schema'

PS C:\> Import-DbaParquet -Path C:\temp\schema.data.parquet -SqlInstance sql2016 -database tempdb -UseFileNameForSchema

Example 6: Will import the contents of C:\temp\schema.data.parquet to table 'testtable' in schema 'schema'

PS C:\> Import-DbaParquet -Path C:\temp\schema.data.parquet -SqlInstance sql2016 -database tempdb -UseFileNameForSchema -Table testtable

Example 7: The Parquet field 'Text' is inserted into SQL column 'FirstName' and Parquet field Number is inserted into...

PS C:\> $columns = @{
>> Text = "FirstName"
>> Number = "PhoneNumber"
>> }
PS C:\> Import-DbaParquet -Path c:\temp\supersmall.parquet -SqlInstance sql2016 -Database tempdb -ColumnMap $columns

The Parquet field ‘Text’ is inserted into SQL column ‘FirstName’ and Parquet field Number is inserted into the SQL Column ‘PhoneNumber’. All other columns are ignored and therefore null or default
values.

Example 8: Performs a full data refresh by truncating the existing table before importing

PS C:\> Import-DbaParquet -Path C:\temp\refresh.parquet -SqlInstance sql001 -Database tempdb -Table LookupData -Truncate

Performs a full data refresh by truncating the existing table before importing. The truncate and import
operations are wrapped in a transaction, so if the import fails, the original data is preserved.

Example 9: Imports Parquet data and adds three static columns (SourceFile, ImportDate, Region) to every row

PS C:\> $static = @{ SourceFile = "sales_2024.parquet"; ImportDate = (Get-Date); Region = "EMEA" }
PS C:\> Import-DbaParquet -Path C:\temp\sales.parquet -SqlInstance sql001 -Database sales -Table SalesData -StaticColumns $static -AutoCreateTable

Imports Parquet data and adds three static columns (SourceFile, ImportDate, Region) to every row.
This is useful for tracking data lineage and tagging imported records with metadata.

Example 10: Imports quickload.parquet with AutoCreateTable

PS C:\> Import-DbaParquet -Path C:\temp\quickload.parquet -SqlInstance sql001 -Database tempdb -Table QuickData -AutoCreateTable

Imports quickload.parquet with AutoCreateTable. After import completes, column sizes are automatically
optimized by querying actual max lengths and altering columns from varchar(MAX) to padded sizes
like varchar(16), varchar(32), varchar(64), etc.

Required Parameters

-SqlInstance

The SQL Server Instance to import data into.

-Database

Specifies the target database for the Parquet import. The database must exist on the SQL Server instance.
Use this to direct your data load to the appropriate database, whether it’s a staging, ETL, or production database.

Optional Parameters

-Path

Specifies the file path to Parquet files for import. Supports single files, multiple files, or pipeline input from Get-ChildItem.

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-Table

Specifies the destination table name. If omitted, uses the Parquet filename as the table name.
The table will be created automatically with -AutoCreateTable using UTF-8 varchar(MAX) columns for strings by default, but for production use, create the table first with proper data types and
constraints.

-Schema

Specifies the target schema for the table. Defaults to ‘dbo’ if not specified.
If the schema doesn’t exist, it will be created automatically when using -AutoCreateTable. This parameter takes precedence over -UseFileNameForSchema.

-Truncate

Removes all existing data from the destination table before importing. The truncate operation is part of the transaction.
Use this for full data refreshes where you want to replace all existing data with the Parquet contents.

-BatchSize

Controls how many rows are sent to SQL Server in each batch during the bulk copy operation. Defaults to 50,000.
Larger batches are generally more efficient but use more memory, while smaller batches provide better granular control and error isolation.

-NotifyAfter

Sets how often progress notifications are displayed during the import, measured in rows. Defaults to 50,000.
Lower values provide more frequent updates but may slow the import slightly, while higher values reduce overhead for very large files.

-TableLock

Acquires an exclusive table lock for the duration of the import instead of using row-level locks.
Improves performance for large imports by reducing lock overhead, but blocks other operations on the table during the import.

-CheckConstraints

Enforces check constraints, foreign keys, and other table constraints during the import. By default, constraints are not checked for performance.
Enable this when data integrity validation is critical, but expect slower import performance.

-FireTriggers

Executes INSERT triggers on the destination table during the bulk copy operation. By default, triggers are not fired for performance.
Use this when your triggers perform essential business logic like auditing, logging, or cascading updates that must run during import.

-KeepIdentity

Preserves identity column values from the Parquet file instead of generating new ones. By default, the destination assigns new identity values.
Use this when migrating data and you need to maintain existing primary key values or referential integrity.

-Column

Imports only the specified columns from the Parquet file, ignoring all others. Column names must match exactly.
Use this to selectively load data when you only need certain fields, reducing import time and storage requirements.

-ColumnMap

Maps Parquet columns to different table column names using a hashtable. Keys are Parquet column names, values are table column names.
Use this when your Parquet headers don’t match your table structure or when importing from systems with different naming conventions.

-KeepOrdinalOrder

Maps columns by position rather than by name matching. The first Parquet column goes to the first table column, second to second, etc.
Use this when column names don’t match but the order is correct, or when dealing with files that have inconsistent naming.

-AutoCreateTable

Creates the destination table automatically if it doesn’t exist, using Parquet schema types for SQL column definitions.
String columns are created as UTF-8 varchar(MAX) by default (or nvarchar(MAX) with -NoUtf8), then automatically optimized based on actual data lengths.
For production use with specific constraints, create tables manually with appropriate data types, indexes, and constraints.

-NoUtf8

Switches AutoCreateTable string columns from UTF-8 varchar to nvarchar.
By default, string columns are created as varchar(MAX) COLLATE Latin1_General_100_BIN2_UTF8.
Use this switch to create string columns as nvarchar(MAX) instead.

-NoColumnOptimize

Skips the automatic column size optimization that runs after AutoCreateTable imports.
By default, AutoCreateTable creates string columns as UTF-8 varchar(MAX) (or nvarchar(MAX) with -NoUtf8) and then shrinks them to fit the imported data.
Use this switch when importing multiple Parquet files into the same auto-created table, so that later files
with longer values are not rejected due to columns being shrunk to fit only the first file’s data.

-NoProgress

Disables the progress bar display during import to improve performance, especially for very large files.
Use this in automated scripts or when maximum import speed is more important than visual progress feedback.

-UseFileNameForSchema

Extracts the schema name from the filename using the first period as a delimiter. For example, ‘sales.customers.parquet’ imports to the ‘sales’ schema.
If no period is found, defaults to ‘dbo’. The schema will be created if it doesn’t exist. This parameter is ignored if -Schema is explicitly specified.

-NoTransaction

Disables the automatic transaction wrapper, allowing partial imports to remain committed even if the operation fails.
Use this for very large imports where you want to commit data in batches, but be aware that failed imports may leave partial data.

-StaticColumns

A hashtable of static column names and values to add to every row.
Useful for tagging imported data with metadata like source filename or import timestamp.
Keys are column names, values are the static values to insert.
Example: @{ SourceFile = “data.parquet”; ImportDate = (Get-Date) }

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.

-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

-Confirm

Prompts you for confirmation before executing any changing operations within the command.

Outputs

PSCustomObject

Returns one object per Parquet file imported. Each object contains comprehensive metrics about the import operation.

Properties:

• ComputerName: The computer name of the SQL Server instance where the Parquet file was imported
• InstanceName: The SQL Server instance name
• SqlInstance: The full SQL Server instance name (computer\instance format)
• Database: The database name where data was imported
• Table: The table name where Parquet data was loaded
• Schema: The schema name containing the target table
• RowsCopied: The total number of rows successfully copied from the Parquet file (int64)
• Elapsed: The elapsed time for the import operation in elapsed time format (automatically formatted as HH:mm:ss.fff)
• RowsPerSecond: The average import rate calculated as total rows divided by elapsed time in seconds (decimal)
• Path: The full file system path of the imported Parquet file