MarkPryceMaherMSFT

/*
IF OBJECT_ID('mpmtest') IS NOT NULL
  BEGIN;
	DROP TABLE mpmtest
END

declare @path varchar(400), @dt datetime2, @credential varchar(500),@outputable varchar(500),@display int, @debug int;
set @path = 'https://cstestwss.blob.core.windows.net/csetestws/delta/demo/'
--set @dt =  convert(datetime2,'2022/07/06 18:37:00'); --getdate(); --  for time travel -- 
set @dt =  getdate(); --  for time travel -- 

MarkPryceMaherMSFT

create proc import_data as
begin

	declare @dummy_datetime datetime2 ;
	declare @sSQL varchar(8000);

		if exists (
		select 1 from sys.schemas s inner join sys.tables t 
		on s.schema_id = t.schema_id 

MarkPryceMaherMSFT

create PROC [dbo].[create_external_table_from_table] @tbname [varchar](200),@schema [varchar](200),@storageacc [varchar](200),
@datafolder [varchar](400),@fileformat [varchar](400),@external_Table_Name [varchar](200),@external_Table_Schema_Name [varchar](200) AS
BEGIN
    SET NOCOUNT ON
/*
This proc creates an external table based on the shape of the table we want to import into.

Usage:
exec [dbo].[create_external_table_from_table]] 'tablename','schema','MyAzureStorage','/path/path/file.csv','FormatCSV','external_tbl_name','ext_schema'

MarkPryceMaherMSFT

CREATE PROC [dbo].[sp_kill_transactions] AS

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
  BEGIN;
	DROP TABLE #stats_ddl;
  END;

CREATE TABLE #stats_ddl
WITH

MarkPryceMaherMSFT

/*
--https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-scoped-credential-transact-sql?view=sql-server-ver15
CREATE DATABASE SCOPED CREDENTIAL AppCred WITH IDENTITY = 'user',   SECRET = '<EnterStrongPasswordHere>';

--https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop#create-external-data-source
CREATE EXTERNAL FILE FORMAT [parquet_snappy] WITH (FORMAT_TYPE = PARQUET, DATA_COMPRESSION = N'org.apache.hadoop.io.compress.SnappyCodec')

CREATE EXTERNAL DATA SOURCE [ds_xxxxxxx] WITH (TYPE = HADOOP, LOCATION = N'wasbs://xxxxx@xxxx.blob.core.windows.net/',  CREDENTIAL = [AppCred])
*/

MarkPryceMaherMSFT

--exec profile_table  'dbo','Copy_into_example_c'

/*
Proc to profile the data in a table.
This proc will give to the min and max values in every column, with is useful when selecting the right numberic data type - bit/int/bigint

It will give you the length of the of the value, which is useful when picking the right variable length datatype - char/varchar/nvarchar

It will also give you the number of distinct values, which is useful for selecting a distribution column.

MarkPryceMaherMSFT

-- size
SELECT DATABASEPROPERTYEX (DB_NAME(), 'ServiceObjective' ) as ServiceObjective
-- How many nodes....
select * from sys.dm_pdw_nodes; -- DWU100 to DWU400

-- distributions
 select * from   sys.pdw_distributions

MarkPryceMaherMSFT

Create PROC [dbo].[wait_for] @seconds [bigint] AS
begin
	declare @quit bit  = 0;
	declare @ConpareDateTime datetime2;
	set @ConpareDateTime = dateadd(s, @seconds, getdate() )

	while(@quit=0)
	begin
		if getdate() > @ConpareDateTime
			set @quit = 1;

MarkPryceMaherMSFT

create PROC [dbo].[gettbldetails] @tblname [varchar](255) AS
begin

SELECT
    [Fully Entity Name]                 = t.full_entity_name,
    [Schema Name]                       = t.schema_name,
    [Entity Name]                       = t.entity_name,
    [Current Distribution Method]       = t.distribution_method,
    [Current Distribution Column]       = ISNULL(t.distribution_column, '-'),

MarkPryceMaherMSFT

/* 
create table [dbo].[round_robin_test1]  
with ( distribution = round_robin) as select * from [dbo].[lineitem_source] where 1 = 0 

insert into [dbo].[round_robin_test1]  
	 select top 158 * from [dbo].[lineitem_source] -- ( just slight more than 32kb)
*/
--exec gettbldetails '%round_robin_test1%'

SELECT