vithalsamp

$ cat sample_script.sql
select * from films where kind = :'kind_val' ;
$ psql -v kind_val='Action' -f 'sample_script.sql'
 code |   title    |  kind  |   len    
-------+------------+--------+----------
 C_102 | Globe1     | Action | 01:40:00
 C_101 | Globe      | Action | 01:30:00
 C_701 | Golden Eye | Action | 01:36:00
 C_103 | Globe2     | Action | 01:30:00
 C_104 | Globe3     | Action | 01:36:00

vithalsamp

training=# SELECT ROW_NUMBER() OVER( ORDER BY code) as ROWNUM, code,title,kind,len from films; 
 rownum | code | title | kind | len 
--------+-------+------------+--------+---------- 
 1 | C_101 | Globe | Action | 01:30:00 
 2 | C_102 | Globe1 | Action | 01:40:00 
 3 | C_103 | Globe2 | Action | 01:30:00 
 4 | C_104 | Globe3 | Action | 01:36:00 
 5 | C_105 | Globe4 | Action | 01:37:00 
 6 | C_106 | Bean | Comedy | 01:20:00 
 7 | C_107 | Bean2 | Comedy | 01:29:00 

vithalsamp

training=# select REGEXP_SUBSTR('abc jjs Updates U 123 U389362','U[0-9][0-9][0-9][0-9][0-9][0-9]'); 
 regexp_substr 
--------------- 
 U389362 
(1 row) 

vithalsamp

training=# select regexp_replace(string, '[^[:digit:]]', ' ') from (select ' My zip is 12345 and id is 389362. Send details to my house # 8/22' as string) a; 
 regexp_replace 
-------------------------------------------------------------------- 
 12345 389362 8 22 
(1 row) 

--Another way to extract the numbers from the string using Redshiftregexp_replace regular expressions

training=# select REGEXP_replace(string, '[a-z/-/A-z/./#/*]', '') from (select ' My zip is 12345 and id is 389362. Send details to my house # 8/22' as string) a; 
 regexp_replace 

vithalsamp

training=# select REGEXP_SUBSTR(string, '(^|[^[:word:]]|[[:space:]])\\d{6}([^[:word:]]|[[:space:]]|$)') from (select 'My zip is 12345 and id is 389362 . Send details to my house # 8/22' as string) a ; 
 regexp_substr 
--------------- 
 389362 
(1 row) 

vithalsamp

Query: alter VIEW action_movies RENAME TO comedy_movie ;


[quickstart.cloudera:21000] > select * from comedy_movie; 
Query: select * from comedy_movie 
+-------+-------+--------+----------+ 
| code | title | kind | len | 
+-------+-------+--------+----------+ 
| C_107 | Bean | Comedy | 01:37:00 | 
| C_106 | MIB | Comedy | 01:31:00 | 

vithalsamp

Query: alter VIEW action_movies AS 
 SELECT * 
 FROM films 
 WHERE kind = 'Comedy' 

[quickstart.cloudera:21000] > select * from action_movies; 
Query: select * from action_movies 
+-------+-------+--------+----------+ 
| code | title | kind | len | 
+-------+-------+--------+----------+ 

vithalsamp

Query: create VIEW action_movies AS 
 SELECT * 
 FROM films 
 WHERE kind = 'Action' 
Fetched 0 row(s) in 0.17s 

-- now check the output

[quickstart.cloudera:21000] > select * from action_movies; 
Query: select * from action_movies 

vithalsamp

CREATE TABLE Test_Compression_Types
(
  key INTEGER NOT NULL encode raw,
  employee_num CHAR(11) NOT NULL encode lzo,
  employee_type CHAR(1) NOT NULL encode runlength,
  employee_status CHAR(1) NOT NULL encode lzo,
  employee_tier CHAR(1) NOT NULL encode lzo,
  service_flag char(1) NOT NULL encode runlength,
  employee_flag char(1) NOT NULL encode ZSTD,
  account_update_date DATE NOT NULL encode delta32k,

vithalsamp

create or replace function udf_nested_func (float, float )
 returns float
stable
as $$
 select udf_sql_bignum ($1, $2) 
$$ language sql;

--Call function

training=# select udf_nested_func(10.3,100.2);