BigData

Hive Extract Numbers using Regular Expression Functions

In my other article, we have seen how to extract date values from a string using Hive regular expressions. The regular expression function is sometime called as regex. The other common uses of regular expression is to extract the numeric values. For example, extract area code or phone numbers from the string data. In this article, we will check how to extract numbers using regular expression functions in Apache Hive. Extract Numbers using Hive Regular Expression Functions When you work on different data sources, you may get requirement to extract…

Comments Off on Hive Extract Numbers using Regular Expression Functions
December 25, 2019
Snowflake

Export Snowflake Table using Python – Example

Snowflake cloud data warehouse provides support for many connectors. For example, Python connector, Spark connector, etc. In my previous articles, we have seen how to use Python connectors, JDBC and ODBC drivers to connect to Snowflake. In this article, we will check how to export Snowflake table using Python with an example. Export Snowflake Table using Python As mentioned, there are many methods such as Python connector, JDBC or ODBC drivers get data from Snowflake tables. Related Articles, How to use Snowflake Python Connector and Example Use Snowflake Python Connector…

Comments Off on Export Snowflake Table using Python – Example
December 24, 2019
BigData

Hive Extract Date using Regular Expression Functions

In my other articles, we have seen how regular expression functions are used to check if a string is numeric. The other common requirements is to extract the date field from the string using Hive regular expression functions. For example, you may get requirement to get date value from the string field. Apache Hive support many functionalities that you can use to extract date type. But, using a regular expression function to extract date from a string is the easiest method. Extract Date using Hive Regular Expression Functions There are…

Comments Off on Hive Extract Date using Regular Expression Functions
December 23, 2019
Snowflake

Snowflake Sequence – How to Create and Use it?

A Sequence is a named object in an individual Snowflake schema and database. These sequences are used to generate unique numbers. You can use sequence to generate unique numbers that can be used as surrogate key values for primary key values. Snowflake Sequence Overview A sequence value can represent a 64-bit two’s complement integer. You can use it wherever you would use numeric values. Snowflake supports user sequences for the four integer types: byteint, smallint, integer, and bigint. For example, you can use sequences in unique columns, primary key columns, etc. You…

Comments Off on Snowflake Sequence – How to Create and Use it?
December 23, 2019
Snowflake

Export Snowflake Table Data to Local CSV format

The exporting tables to local system is one of the common requirements. You may need to export Snowflake table to analyze the data or transport it to a different team. You can export the Snowflake schema in different ways, you can use COPY command, or Snowsql command options. In this articles, we will check how to export Snowflake table data to a local CSV format. Using the COPY command may be the fastest method. But, it will unload tables to S3 location. You need to have an AWS subscription to…

Comments Off on Export Snowflake Table Data to Local CSV format
December 22, 2019
Snowflake

Snowflake Extract Date using Regular Expression Functions

In my other article, we have seen how to extract number or numeric values from a string. The other common requirement is to extract date from a string data. For example, you may get requirement to extract date of birth from the string field. Snowflake supports various date functions to validate a date value. In this article, we will check how to extract date from a string field using Snowflake regular expression functions. Extract Date using Snowflake Regular Expression Functions In my other article, we have discussed on Snowflake regular…

Comments Off on Snowflake Extract Date using Regular Expression Functions
December 21, 2019
Snowflake

Snowflake Extract Numbers using Regular Expression Functions

There are various requirements to extract numbers from string value. This requirement comes when you work with different data source. For example, heterogeneous data may contain many unwanted values and requirements will be to get only numbers or numeric values. Snowflake supports many built-in and regular expression functions. In this article, we will check how to extract numbers or numeric values from a string using Snowflake regular expression functions. Extract Numbers using Snowflake Regular Expression Functions In my other article, we have discussed on Snowflake regular expressions. We will use…

Comments Off on Snowflake Extract Numbers using Regular Expression Functions
December 21, 2019
Snowflake

Snowflake NOT NULL Constraint and Syntax

Similar to most of the MPP databases, Snowflake cloud database allows you to define constraints. The Snowflake database does not enforce constraints like primary key, foreign key and unique key. But, it does enforce the NOT NULL constraint. In this article, we will check Snowflake NOT NULL constraint, its syntax and usage. Snowflake NOT NULL Constraint Constraints other than NOT NULL are created as disabled. Snowflake enforces only NOT NULL. You can create NOT NULL constraint while creating tables in the cloud database. A Snowflake table can have multiple NOT…

Comments Off on Snowflake NOT NULL Constraint and Syntax
December 20, 2019
Snowflake

Snowflake Unique Key Constraint and Syntax

Similar to Snowflake primary key and foreign key, unique key constraint is informational only. It is not enforced when you insert rows to the table. The unique key metadata information is used by the Snowflake optimizer to come up with the optimal execution plan. Snowflake Unique Key Constraint You can create Unique key constraint while creating tables in the Snowflake cloud database but it will not be enforced while loading tables. The Snowflake query planner uses these constraints to create a better query execution plan. Similar to the foreign key,…

Comments Off on Snowflake Unique Key Constraint and Syntax
December 20, 2019
Snowflake

Snowflake Foreign Key Constraint and Syntax

Similar to the primary key constraint, the Snowflake foreign key constraint is also information only. It is not enforced when you insert the data into a table. Constraints other than NOT NULL are created as disabled. Snowflake Foreign Key Constraint You can create the foreign key while creating tables on the Snowflake cloud data warehouse. But, foreign key will not be enforced when you load the tables. However, constraints provide valuable metadata. The optimizer uses the foreign keys to create an optimal execution plan. A table can have multiple unique…

Comments Off on Snowflake Foreign Key Constraint and Syntax
December 20, 2019