ETL design question

Question

Post reply

ETL design question

KoldCoffee

SSCoach

Points: 18971
More actions
October 13, 2014 at 10:37 am

#291702

Source: flat files (.txt and csv). Destination: a single SQL Server 2008RS2 table
(1) I have straight across imported the flatfiles into SQL Server staging tables so that I can examine contents. For each flat file there is a staging table (1 to 1 relationship)
(2) after examining staging tables a relational database schema has emerged indicating the flat files come from a relational database. Have found how to join up the flat files to generate the rows needed to populate the final destination table.
(3) built a SELECT INTO query. The format of the ETL query is as follows:
SELECT TOP 1000
row1 based on logic,
row2 based on logic and substring transformation,
row3, row4, row 20, etc.
INTO
FROM
<TABLES various JOINS>
The above INSERT statement works ONLY if I constrain to 1000 rows. But, if I remove constraint it runs for hours and ultimately brings over no information.
There are millions of rows against which I must run the query logic so that I bring over the data as needed and don't know how I would break up the source into 1000 row blocks. What approach to take or technology to use given volume of source data?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

How many rows are we talking about here?

How many flat files (aka how many joins)?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

KoldCoffee SSCoach Points: 18971 More actions · Answer 2

This is literally the query: 7 JOINS and millions of rows.

--KEYWORDS

SELECT TOP 1000

publisher_name as account,

reverse(left(reverse(publisher_name),(charindex('-',reverse(publisher_name),0))-2)) as publisher,

C.campaign_id,

channel_bing_pixel = CASE WHEN D.dimension_name = 'Channel_Bing_Pixel' then value END,

google_conversion_label = CASE WHEN D.dimension_name = 'Channel_Google_ConversionLabel' then value END,

updated_on = getdate(),

created_on = getdate(),

group_location_display_name = CASE when D.dimension_name = 'group_location_display_name' THEN value END,

headline = NULL,

K.keyword_url,

REVERSE(LEFT(REVERSE(LEFT(K.keyword_url, CHARINDEX('&pcrid',K.keyword_url + '&pcrid')-1)),8)) as unique_id,

K.keyword,

G.group_name,

FK.impressions,

C.campaign_start_date as campaign_created_on,

C.last_modified as campaign_updated_on,

match_type = K.keyword_match_type,

group_subdomain = CASE

WHEN D.dimension_name = 'Group_Subdomain' THEN D.value

ELSE 'UNKNOWN' END,

group_care_types = CASE

WHEN D.dimension_name = 'Group_Caretypes' THEN D.value

ELSE 'UNKNOWN' END,

group_location_type = CASE

WHEN D.dimension_name = 'Group_Location_Type' THEN D.value

ELSE 'UNKNOWN' END,

group_location_county = CASE

WHEN D.dimension_name = 'Group_Location_County' THEN D.value

ELSE 'UNKNOWN' END,

group_location_state = CASE

WHEN D.dimension_name = 'Group_Location_State' THEN D.value

ELSE 'UNKNOWN' END,

group_location_city = CASE

WHEN D.dimension_name = 'Group_Location_City' THEN D.value

ELSE 'UNKNOWN' END,

group_location_neighborhood = CASE

WHEN D.dimension_name ='Group_Location_Neighborhood' THEN D.value

ELSE 'UNKNOWN' END,

group_location_class = CASE

WHEN D.dimension_name ='Group_Keyword_Class' THEN D.value

ELSE 'UNKNOWN' END,

group_network = CASE

WHEN D.dimension_name ='Group_Network' THEN D.value

ELSE 'UNKNOWN' END,

channel_phone_number = CASE

WHEN D.dimension_name ='Channel_Phone_Number' THEN D.value

ELSE 'UNKNOWN' END,

C.campaign_name,

C.campaign_status

FROM [24632_settings_keyword] K

JOIN [24632_settings_group] G ON K.group_id = G.group_id

JOIN [24632_settings_campaign]C ON G.campaign_id = C.campaign_id

JOIN [24632_settings_dimension] D ON G.group_id = D.object_id

JOIN [24632_settings_creative] SC ON G.group_id = SC.group_id

JOIN [24632_settings_placement] SP ON G.group_id = SP.group_id

JOIN [dbo].[24632_settings_publisher] P on c.[marin_pca_id] = p.[marin_pca_id]

--gets keyword impressions

JOIN [24632_fact_keyword] FK ON K.group_id = FK.group_id

UNION ALL X2

to bring over Creatives and Impressions.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Time to take a look at the execution plan. Remove the TOP and get an estimated execution plan.

Maybe you need some indexes to speed up the query.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

KoldCoffee SSCoach Points: 18971 More actions · Answer 4

ok, I forgot to mention, I have definitely created indexes. This was necessary so that I could even run them as TOP 1000 queries. They are as follows.

create index I_groupid on [24632_settings_group] (group_id);

create index I_groupid on [24632_settings_keyword] (group_id);

create index I_campaignid on [24632_settings_campaign] (campaign_id);

create index I_creativeid on [24632_settings_creative] (creative_id);

create index I_placementid on [24632_settings_placement] (placement_id);

create index I_keywordid on [24632_settings_keyword] (keyword_id);

create index I_creativeid on [24632_fact_creative] (creative_id);

create index I_placementid on [24632_fact_placement] (placement_id);

create index I_keywordid on [24632_fact_keyword] (keyword_id);

create index I_marin_pca_id on [24632_settings_campaign] (marin_pca_id);

create clustered index I_marin_pca_id ON [24632_settings_publisher] (marin_pca_id);

create nonclustered index I_nc_groupid on [24632_settings_creative] (group_id);

create nonclustered index I_nc_groupid on [24632_settings_placement] (group_id);

create nonclustered index I_nc_groupid on [24632_settings_keyword] (group_id);

create nonclustered index I_nc_groupid on [24632_fact_creative] (group_id);

create nonclustered index I_nc_groupid on [24632_fact_placement] (group_id);

create nonclustered index I_nc_groupid on [24632_fact_keyword] (group_id);

Here's the estimated execution plan with the above indexes, without TOP 1000:

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.1600.1">

<BatchSequence>

<Batch>

<Statements>

<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />

<QueryPlan CachedPlanSize="208" CompileTime="1428" CompileCPU="1427" CompileMemory="23000">

<MissingIndexes>

<MissingIndexGroup Impact="86.9351">

<MissingIndex Database="[CampaignManagement]" Schema="[dbo]" Table="[24632_settings_dimension]">

<ColumnGroup Usage="EQUALITY">

<Column Name="[object_id]" ColumnId="3" />

</ColumnGroup>

<ColumnGroup Usage="INCLUDE">

<Column Name="[dimension_name]" ColumnId="5" />

<Column Name="[value]" ColumnId="6" />

</ColumnGroup>

</MissingIndex>

</MissingIndexGroup>

</MissingIndexes>

<RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="72094" EstimatedTotalSubtreeCost="500.325" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">

<OutputList>

<ColumnReference Database="[CampaignManagement]" Schema="[dbo]" Table="[24632_settings_keyword]" Alias="[K]" Column="keyword" />

<ColumnReference Database="[CampaignManagement]" Schema="[dbo]" Table="[24632_settings_keyword]" Alias="[K]" Column="keyword_match_type" />

<ColumnReference Database="[CampaignManagement]" Schema="[dbo]" Table="[24632_settings_keyword]" Alias="[K]" Column="keyword_url" />

<ColumnReference Database="[CampaignManagement]" Schema="[dbo]" Table="[24632_settings_group]" Alias="[G]" Column="group_name" />

<ColumnReference Database="[CampaignManagement]" Schema="[dbo]" Table="[24632_settings_campaign]" Alias="[C]" Column="campaign_id" />

<ColumnReference Database="[CampaignManagement]" Schema="[dbo]" Table="[24632_settings_campaign]" Alias="[C]" Column="campaign_name" />

<ColumnReference Database="[CampaignManagement]" Schema="[dbo]" Table="[24632_settings_campaign]" Alias="[C]" Column="campaign_status" />

<ColumnReference Database="[CampaignManagement]" Schema="[dbo]" Table="[24632_settings_campaign]" Alias="[C]" Column="campaign_start_date" />

<ColumnReference Database="[CampaignManagement]" Schema="[dbo]" Table="[24632_settings_campaign]" Alias="[C]" Column="last_modified" />

<ColumnReference Database="[CampaignManagement]" Schema="[dbo]" Table="[24632_settings_publisher]" Alias="[P]" Column="publisher_name" />

<ColumnReference Database="[CampaignManagement]" Schema="[dbo]" Table="[24632_fact_keyword]" Alias="[FK]" Column="impressions" />

<ColumnReference Column="Expr1024" />

<ColumnReference Column="Expr1025" />

<ColumnReference Column="Expr1026" />

<ColumnReference Column="Expr1027" />

<ColumnReference Column="Expr1028" />

<ColumnReference Column="Expr1029" />

<ColumnReference Column="Expr1030" />

<ColumnReference Column="Expr1031" />

<ColumnReference Column="Expr1032" />

<ColumnReference Column="Expr1033" />

<ColumnReference Column="Expr1034" />

<ColumnReference Column="Expr1035" />

<ColumnReference Column="Expr1036" />

<ColumnReference Column="Expr1037" />

<ColumnReference Column="Expr1038" />

<ColumnReference Column="Expr1039" />

<ColumnReference Column="Expr1040" />

<ColumnReference Column="Expr1041" />

</OutputList>

<ComputeScalar>

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1027" />

<ScalarOperator ScalarString="getdate()">

<Identifier>

<ColumnReference Column="ConstExpr1042">

<ScalarOperator>

<Intrinsic FunctionName="getdate" />

</ScalarOperator>

</ColumnReference>

</Identifier>

</ScalarOperator>

</DefinedValue>

<DefinedValue>

<ColumnReference Column="Expr1028" />

<ScalarOperator ScalarString="getdate()">

<Identifier>

<ColumnReference Column="ConstExpr1043">

<ScalarOperator>

<Intrinsic FunctionName="getdate" />

</ScalarOperator>

</ColumnReference>

</Identifier>

</ScalarOperator>

</DefinedValue>

<DefinedValue>

<ColumnReference Column="Expr1030" />

<ScalarOperator ScalarString="NULL">

<Const ConstValue="NULL" />

</ScalarOperator>

</DefinedValue>

</DefinedValues>

<RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="72074" EstimatedTotalSubtreeCost="500.325" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">