MarkPryceMaherMSFT

-- Create test table
create table datetest (d varchar(50), dt DATETIME2(6));

insert into datetest SELECT 'Central European Standard Time', CONVERT(DATETIME2(6), '2022-03-27T01:01:00', 126)
AT TIME ZONE 'Central European Standard Time';

insert into datetest SELECT 'Pacific Standard Time' , CONVERT(DATETIME2(6), '2022-03-27T01:01:00', 126)
AT TIME ZONE 'Pacific Standard Time';

select d as TimeZone, dt as DateTime_without_TimeZone, 

MarkPryceMaherMSFT

SELECT
SchName,
tblName,
'CREATE view [' + SchName + '].[vw_' + tblName + '] as SELECT  ' + STRING_AGG(colname + ' '+ 
case coltype
when 'varchar' then ' COLLATE Latin1_General_100_CI_AI_SC as ' + colname  
else '' 
end 
  , ', ') + ' from [' + SchName + '].[' + tblName + '];' AS DDLScript
FROM (

MarkPryceMaherMSFT

MarkPryceMaherMSFT

import json
import time
import struct
import sqlalchemy
import pyodbc
import notebookutils
import pandas as pd
from pyspark.sql import functions as fn
from datetime import datetime
import sempy.fabric as fabric

MarkPryceMaherMSFT

########################################################################################################
# Sample script to call the syncronisation between the Fabric Lakehouse and the SQL Endpoint
#        
## THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
#
#  This script is a workaround until the documented API is released: https://learn.microsoft.com/en-us/fabric/release-plan/data-warehouse#refresh-sql-analytics-endpoint-rest-api
#
#sempy version 0.4.0 or higher
!pip install semantic-link --q 
import json

MarkPryceMaherMSFT

MarkPryceMaherMSFT

MarkPryceMaherMSFT

create_new_table("control", False, False, False, False, False, False, False, False, False )
create_new_table("control", True, False, False, False, False, False, False, False, False )
create_new_table("control", False, True, False, False, False, False, False, False, False )
create_new_table("control", True, True, False, False, False, False, False, False, False )
create_new_table("control", False, False, True, False, False, False, False, False, False )
create_new_table("control", True, False, True, False, False, False, False, False, False )
create_new_table("control", False, True, True, False, False, False, False, False, False )
create_new_table("control", True, True, True, False, False, False, False, False, False )
create_new_table("control", False, False, False, True, False, False, False, False, False )
create_new_table("control", True, False, False, True, False, False, False, False, False )

MarkPryceMaherMSFT

def create_new_table(tablename, columnmapping,delete_vectors,partitions,columnmappingbyid,liquidclustering,optimize,vacuum,writeStatsAsStruct,writeStatsAsJson):
    # What the function does goes here
    # use the default sample table, publicholidays
    tablename = "PARQUET_2_0"
    
    if columnmapping==True and columnmappingbyid==False:
        tablename=tablename+"_columnmapping_by_name"
    if delete_vectors==True:
        tablename=tablename+"_delete_vectors"    
    if partitions==True:

MarkPryceMaherMSFT