INSERT INTO tables with VALUES option as achieved with other SQL variants is not supported in Spark SQL as of now. For single record inserts the below example provides two options:
Use case: sometimes files are processed were nulls are represented with text like “NULL” meaning the field is not actually empty or null.
Below are some python functions and a test demonstrating functionality.
A dynamic pivot table means you do not need to define hard coded column names as a dynamic query will fetch the field values from a column and use them as the column names while pivoting the source table.
Good thing there are some code examples below you can just steal and alter as you need.
The first example will just return as a SELECT, the second example will write the results to a global temp table called ##Result.
A use case for this might be a continuous requirement to pivot a table however the column name requirements keep changing as field values change.
Example 1: Return as SELECT
Example 2: Write output to a table
The following is a code snippet that would create a table in a “sales” schema called customer.
If no reference to a schema is given the table will be created in the default Spark location.
So you have a PowerShell script and you just want to run it without messing around with permissions, policies, signing it or any other crap. (Yes yes I know all those things are vital for system wide security but you’re in a hurry damn it!)
Right click PowerShell and run as administrator.
When the terminal is open run the following line:
When prompted type the letter A and press Enter (if applicable).
Run the below including “&” at start of line with reference to your script, i.e.
Below is example PowerShell code to create a directory structure from file creation dates.
A use case for this code might be a need to organize a collection of files stored on an SFTP server or NAS. The code can be modified to loop through the files putting each one into sub directories corresponding to nested folders organized into Year\Month\Day folders.
To use the logic first create a test folder on your computer.
Then copy the logic below to a text file editor and find and replace the following with reference to your test folder location:
Then save the code below as a PowerShell file called “DirFromDate.ps1” into your test folder.
To then run the file open up PowerShell and paste the following line:
Hit return and then enter the letter A if requested.
Then enter the following replacing “YourTestDirectory” with your actual test directory location:
The script below will, providing the login you are using has adequate permissions, return the schema permissions a user has.
Commented out at the end of the script are examples of the types of permission you can assign, again providing the login you are using has adequate permissions.
Updating/replacing or removing an email address from SSRS subscriptions manually is far too time consuming and tedious. Use the below script instead to either update/replace an email address with a new one across all subscriptions or remove the email address from all subscriptions.
To exclude subscriptions, i.e. keep the email address active for a particular subscription, find the subscription Id for that subscription and include it in the WHERE clause. Remember to uncomment that line in order for the clause to be active.
If you’re worried about messing anything up then back up the table before running the script!
Update/replace or remove an email address:
Some systems never heard of Y2K.
Many programs in operation today, terrible programs written by lazy developers, still represent four-digit years with only the final two digits, making the year 2000 indistinguishable from 1900.
If you’re consuming data from a source system using an incomplete date format, and you’re doing your job properly, you’ll want to correct for that.
Below is an ADF expression example that will correct date field values that relate to the year 2000 onward by prefixing 20 to the year, e.g. 21 becomes 2021.