If First day of week should start from Monday, change the subtraction/addition date to 1900-01-08 --First day of the week as Monday select date_sub('2018-09-12',pmod(datediff('2018-09-12','1900-01-08'),7)); +-------------+--+ | _c0 | +-------------+--+ | 2018-09-10 | +-------------+--+ --Last day of the week as Sunday select date_add('2018-09-12',6 - pmod(datediff('2018-09-12','1900-01-08'),7)); +-------------+--+ | _c0 | +-------------+--+ | 2018-09-16 | +-------------+--+ ... View more

You can write a custom UDF in Hive to pick any day of the week. You can refer lastDay UDF code as an example // Code in Custom UDF FirstDayOfWeek SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd"); Calendar calendar = Calendar.getInstance(); calendar.setTime(formatter.parse("2018-09-16")); // Actual Date string column here calendar.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY); System.out.println(calendar.getTime()); ... View more

Create text table with comma(,) as field delimiter create table textcomma(age int, name string) row format delimited fields terminated by ',' stored as textfile; insert into textcomma values(1,'a'),(2,'b'),(3,'c'); Option 1 : CTAS text table with pipe(|) as field delimiter create table textpipe row format delimited fields terminated by '|' stored as textfile as select * from textcomma; # hadoop fs -cat /apps/hive/warehouse/textcomma/000000_0 1,a 2,b 3,c # hadoop fs -cat /apps/hive/warehouse/textpipe/000000_0 1|a 2|b 3|c Option 2 : Insert overwrite directory to write textfile with pipe(|) delimiter INSERT OVERWRITE DIRECTORY '/tmp/text-pipe' ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE SELECT * FROM textcomma; # hadoop fs -cat /tmp/text-pipe/000000_0 1|a 2|b 3|c If it helps to solves your query, accept the answer. It might help others. ... View more

Is it MR job ? what is the value for hive.execution.engine? Are you using specific queue to launch this job ? It seems to be resource unavailable issue. If AppMaster is launched, can you collect application_1536988895253_0006 logs & check why task container's are not getting launched yet. ... View more

There are multiple ways to populate avro tables. 1) Insert into avro_table values(<col1>,<col2>..,<colN>) -- This way hive will write avro files. 2) Generating avro files & copying directly to '/tmp/staging', You can read avro documentation to write avro files directly into hdfs path. Avro Reader/Writer APIs will take care of storing & retrieving records, we don't need to explicitly specify delimiters for avro files. ... View more

you can create external table with location & write the text files directly on that path. eg., create external table staging1(id struct<tid:string,action:string,createdts:timestamp>, cid string, anumber string) row format delimited fields terminated by ',' collection items terminated by '|' stored as textfile LOCATION '/tmp/staging/'; All text files can be directly written at /tmp/staging/ by kafka or flume If Kafka or flume will be able to generate Avro files, then you can skip the staging table & create external avro table & write avro files directly on to external table location. ... View more

Yes. File content will be # hadoop fs -cat /tmp/data1.txt 1|success|2018-09-12 17:45:39.69,3,12345 Then you need to load the content into staging table using below command load data inpath '/tmp/data1.txt' into table staging; Then from staging, you need to load it into actual avro table using below command insert into testtbl select * from staging; If my answer helped you to resolve your issue, you can accept it. It will be helpful for others. ... View more