Microsoft SQL Server
Bruin supports Microsoft SQL Server as a data platform.
NOTE
We tend to use "MS SQL" interchangeably to refer to Microsoft SQL Server, apologies for any confusion.
Connection
In order to set up a SQL Server connection in Bruin, you need to add a configuration item to connections
in the .bruin.yml
file complying with the following schema.
connections:
mssql:
- name: "connection_name"
username: "mssql_user"
password: "XXXXXXXXXX"
host: "mssql_host.somedomain.com"
port: 1433
database: "dev"
SQL Server Assets
ms.sql
Runs a materialized SQL Server asset or an SQL script. For detailed parameters, you can check Definition Schema page.
Examples
Run an MS SQL script to generate sales report
/* @bruin
name: sales_report
type: ms.sql
@bruin */
with monthly_sales as (
select
product_id,
year(order_date) as order_year,
month(order_date) as order_month,
sum(quantity) as total_quantity,
sum(price) as total_sales
from sales.orders
group by product_id, year(order_date), month(order_date)
)
select
product_id,
order_year,
order_month,
total_quantity,
total_sales
from monthly_sales
order by order_year, order_month;
ms.seed
ms.seed
is a special type of asset used to represent CSV files that contain data that is prepared outside of your pipeline that will be loaded into your MSSQL database. Bruin supports seed assets natively, allowing you to simply drop a CSV file in your pipeline and ensuring the data is loaded to the MSSQL database.
You can define seed assets in a file ending with .yaml
:
name: dashboard.hello
type: ms.seed
parameters:
path: seed.csv
Parameters:
path
: Thepath
parameter is the path to the CSV file that will be loaded into the data platform. path is relative to the asset definition file.
Examples: Load csv into a MSSQL database
The examples below show how to load a CSV into an MSSQL database.
name: dashboard.hello
type: ms.seed
parameters:
path: seed.csv
Example CSV:
name,networking_through,position,contact_date
Y,LinkedIn,SDE,2024-01-01
B,LinkedIn,SDE 2,2024-01-01