Skip to content

Instantly share code, notes, and snippets.

@ljtill

ljtill/sample.sql

Last active April 17, 2023 10:57
Show Gist options
  • Save ljtill/50c4a5fafd47caf4b03d64697821d848 to your computer and use it in GitHub Desktop.
Save ljtill/50c4a5fafd47caf4b03d64697821d848 to your computer and use it in GitHub Desktop.
Download ZIP
Provides the ability to query Blob (object) Storage with SQL Server
Raw
sample.sql
/*
Encryption
*/
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';
SELECT * FROM sys.symmetric_keys;
/*
Credential
*/
CREATE DATABASE SCOPED CREDENTIAL StorageAccount
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '';
SELECT * FROM sys.database_scoped_credentials;
/*
Data Source
*/
CREATE EXTERNAL DATA SOURCE StorageAccount
WITH ( TYPE = BLOB_STORAGE,
LOCATION = 'https://{name}.blob.core.windows.net/{container}',
CREDENTIAL = StorageAccount
);
SELECT * FROM sys.external_data_sources;
/*
Query
*/
SELECT * FROM
OPENROWSET (
BULK 'dataset/daily/{prefix}/daily_{guid}.csv',
DATA_SOURCE = 'StorageAccount',
FORMAT = 'CSV',
FORMATFILE='usage.fmt',
FORMATFILE_DATA_SOURCE = 'StorageAccount',
FIRST_ROW = 2
) AS DataFile;
/*
Insert
*/
BULK INSERT dbo.daily
FROM 'dataset/daily/{prefix}/daily_{guid}.csv'
WITH (DATA_SOURCE = 'StorageAccount',
FORMAT = 'CSV',
FORMATFILE='usage.fmt',
FORMATFILE_DATA_SOURCE = 'StorageAccount',
FIRST_ROW = 2
);
BULK INSERT dbo.daily
FROM 'dataset/daily/{generated}/{generated}/{guid}/000001.csv'
WITH (DATA_SOURCE = 'StorageAccount',
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
);
/*
Query
*/
SELECT * FROM dbo.daily;
/*
Tables
*/
CREATE TABLE dbo.daily (
[invoiceId] [nvarchar](1) NULL,
[previousInvoiceId] [nvarchar](1) NULL,
[billingAccountId] [nvarchar](50) NOT NULL,
[billingAccountName] [nvarchar](50) NOT NULL,
[billingProfileId] [nvarchar](50) NOT NULL,
[billingProfileName] [nvarchar](50) NOT NULL,
[invoiceSectionId] [nvarchar](50) NOT NULL,
[invoiceSectionName] [nvarchar](50) NOT NULL,
[resellerName] [nvarchar](1) NULL,
[resellerMpnId] [nvarchar](1) NULL,
[costCenter] [nvarchar](50) NOT NULL,
[billingPeriodEndDate] [nvarchar](1) NULL,
[billingPeriodStartDate] [nvarchar](1) NULL,
[servicePeriodEndDate] [date] NOT NULL,
[servicePeriodStartDate] [date] NOT NULL,
[date] [date] NOT NULL,
[serviceFamily] [nvarchar](50) NOT NULL,
[productOrderId] [nvarchar](50) NOT NULL,
[productOrderName] [nvarchar](50) NOT NULL,
[consumedService] [nvarchar](50) NOT NULL,
[meterId] [nvarchar](50) NOT NULL,
[meterName] [nvarchar](50) NOT NULL,
[meterCategory] [nvarchar](50) NOT NULL,
[meterSubCategory] [nvarchar](50) NOT NULL,
[meterRegion] [nvarchar](50) NULL,
[ProductId] [nvarchar](50) NOT NULL,
[ProductName] [nvarchar](100) NOT NULL,
[SubscriptionId] [nvarchar](50) NOT NULL,
[subscriptionName] [nvarchar](50) NOT NULL,
[publisherType] [nvarchar](50) NOT NULL,
[publisherId] [nvarchar](1) NULL,
[publisherName] [nvarchar](50) NOT NULL,
[resourceGroupName] [nvarchar](50) NULL,
[ResourceId] [nvarchar](150) NOT NULL,
[resourceLocation] [nvarchar](50) NULL,
[location] [nvarchar](50) NOT NULL,
[effectivePrice] [float] NOT NULL,
[quantity] [float] NOT NULL,
[unitOfMeasure] [nvarchar](50) NOT NULL,
[chargeType] [nvarchar](50) NOT NULL,
[billingCurrency] [nvarchar](50) NOT NULL,
[pricingCurrency] [nvarchar](50) NOT NULL,
[costInBillingCurrency] [float] NOT NULL,
[costInPricingCurrency] [float] NOT NULL,
[costInUsd] [float] NOT NULL,
[paygCostInBillingCurrency] [float] NOT NULL,
[paygCostInUsd] [float] NOT NULL,
[exchangeRatePricingToBilling] [tinyint] NOT NULL,
[exchangeRateDate] [date] NOT NULL,
[isAzureCreditEligible] [nvarchar](50) NOT NULL,
[serviceInfo1] [nvarchar](1) NULL,
[serviceInfo2] [nvarchar](1) NULL,
[additionalInfo] [nvarchar](50) NULL,
[tags] [nvarchar](1) NULL,
[PayGPrice] [float] NOT NULL,
[frequency] [nvarchar](50) NOT NULL,
[term] [nvarchar](1) NULL,
[reservationId] [nvarchar](1) NULL,
[reservationName] [nvarchar](1) NULL,
[pricingModel] [nvarchar](50) NOT NULL,
[unitPrice] [float] NOT NULL,
[costAllocationRuleName] [nvarchar](1) NULL,
[benefitId] [nvarchar](1) NULL,
[benefitName] [nvarchar](1) NULL,
[provider] [nvarchar](50) NOT NULL
) ON [PRIMARY]
DROP TABLE dbo.daily;
Raw
usage.fmt
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="13" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="14" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="11"/>
<FIELD ID="15" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="11"/>
<FIELD ID="16" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="11"/>
<FIELD ID="17" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="18" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="19" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="20" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="21" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="22" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="24" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="25" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="26" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="27" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="200" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="28" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="29" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="30" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="31" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="32" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="33" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="34" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="300" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="35" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="36" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="37" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30"/>
<FIELD ID="38" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30"/>
<FIELD ID="39" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="40" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="41" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="42" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="43" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30"/>
<FIELD ID="44" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30"/>
<FIELD ID="45" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30"/>
<FIELD ID="46" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30"/>
<FIELD ID="47" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30"/>
<FIELD ID="48" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="5"/>
<FIELD ID="49" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="11"/>
<FIELD ID="50" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="51" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="52" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="53" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="54" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="55" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30"/>
<FIELD ID="56" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="57" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="58" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="59" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="60" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="61" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30"/>
<FIELD ID="62" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="63" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="64" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="65" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="invoiceId" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="2" NAME="previousInvoiceId" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="billingAccountId" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="4" NAME="billingAccountName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="5" NAME="billingProfileId" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="6" NAME="billingProfileName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="7" NAME="invoiceSectionId" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="8" NAME="invoiceSectionName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="9" NAME="resellerName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="10" NAME="resellerMpnId" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="11" NAME="costCenter" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="12" NAME="billingPeriodEndDate" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="13" NAME="billingPeriodStartDate" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="14" NAME="servicePeriodEndDate" xsi:type="SQLDATE"/>
<COLUMN SOURCE="15" NAME="servicePeriodStartDate" xsi:type="SQLDATE"/>
<COLUMN SOURCE="16" NAME="date" xsi:type="SQLDATE"/>
<COLUMN SOURCE="17" NAME="serviceFamily" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="18" NAME="productOrderId" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="19" NAME="productOrderName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="20" NAME="consumedService" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="21" NAME="meterId" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="22" NAME="meterName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="23" NAME="meterCategory" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="24" NAME="meterSubCategory" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="25" NAME="meterRegion" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="26" NAME="ProductId" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="27" NAME="ProductName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="28" NAME="SubscriptionId" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="29" NAME="subscriptionName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="30" NAME="publisherType" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="31" NAME="publisherId" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="32" NAME="publisherName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="33" NAME="resourceGroupName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="34" NAME="ResourceId" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="35" NAME="resourceLocation" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="36" NAME="location" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="37" NAME="effectivePrice" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="38" NAME="quantity" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="39" NAME="unitOfMeasure" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="40" NAME="chargeType" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="41" NAME="billingCurrency" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="42" NAME="pricingCurrency" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="43" NAME="costInBillingCurrency" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="44" NAME="costInPricingCurrency" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="45" NAME="costInUsd" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="46" NAME="paygCostInBillingCurrency" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="47" NAME="paygCostInUsd" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="48" NAME="exchangeRatePricingToBilling" xsi:type="SQLTINYINT"/>
<COLUMN SOURCE="49" NAME="exchangeRateDate" xsi:type="SQLDATE"/>
<COLUMN SOURCE="50" NAME="isAzureCreditEligible" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="51" NAME="serviceInfo1" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="52" NAME="serviceInfo2" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="53" NAME="additionalInfo" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="54" NAME="tags" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="55" NAME="PayGPrice" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="56" NAME="frequency" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="57" NAME="term" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="58" NAME="reservationId" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="59" NAME="reservationName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="60" NAME="pricingModel" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="61" NAME="unitPrice" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="62" NAME="costAllocationRuleName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="63" NAME="benefitId" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="64" NAME="benefitName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="65" NAME="provider" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment