In this blog post we will show case Flexter, Sonra’s ETL tool for complex XML/JSON data standards. We will convert a large number of XML documents into a relational data warehouse. The documents contain information about clinical trials. The XML files are publicly available on ClinicalTrials.gov. As of March 2019, 299,015 XML documents are available for download. This translates to ...
Interested in Big Data, Data Warehouses, Business Intelligence, Data Analytics? Want to learn more about data warehousing, data analytics, and cloud computing (AWS, Google, Azure)? Sonra Intelligence is looking for a computer science intern to join our growing team based in Dublin 7 Grangegorman DIT campus Starting asap The position is full time. Working remotely is an option. Duration: 3-6 ...
Let’s have a look at the evolution of cloud data warehousing. It’s hard to believe, but just a few years ago all data warehouses were run on-premise. Some of them on expensive and highly engineered MPP appliances. Very fast. Not cheap. Smaller data warehouses would run on relational databases such as Oracle or SQL Server, which had been originally engineered ...
Here is a simple SQL script that will generate a date dimension in Snowflake. We are using the built-in GENERATOR and SEQ function. Start date for the dimension is 2017-01-01.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
WITH CTE_MY_DATE AS ( SELECT DATEADD(HOUR, SEQ4(), '2017-01-01 00:00:00') AS MY_DATE FROM TABLE(GENERATOR(ROWCOUNT=>20000)) ) SELECT TO_DATE(MY_DATE) as date ,TO_TIME(MY_DATE) as time ,TO_TIMESTAMP(MY_DATE) as datetime ,YEAR(MY_DATE) as year ,MONTH(MY_DATE) as month ,MONTHNAME(MY_DATE) as monthname ,DAY(MY_DATE) as day ,DAYOFWEEK(MY_DATE) as dayofweek ,WEEKOFYEAR(MY_DATE) as weekofyear ,DAYOFYEAR(MY_DATE) as dayofyear ,HOUR(MY_DATE) as hour FROM CTE_MY_DATE ; |
