tag:blogger.com,1999:blog-494502301850787022.post4263453499819983354..comments2024-03-12T01:28:13.254-07:00Comments on Kick Start Hadoop: Implementing basic SQL Update statement in HiveBejoy KShttp://www.blogger.com/profile/03581597227249520580noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-494502301850787022.post-70533410231198524142015-03-10T04:51:47.560-07:002015-03-10T04:51:47.560-07:00I created 2 tables emp (name,id) and dept (name ,i...I created 2 tables emp (name,id) and dept (name ,id) with following data,<br /><br />(emp1,1),(emp2,2),(emp3,3)<br /> AND<br />(dept1,1),(dept2,2),(dept3,3),(dept4,4),(dept5,5),(dept6,6).<br /><br />I wish to update emp name where emp.id=dept.id and update it with dept name<br /><br />I wrote following query:<br /><br />INSERT OVERWRITE TABLE emp <br />SELECT e.id,<br />CASE WHEN e.id = d.id THEN d.name ELSE e.name END AS name FROM dept AS d,emp as e;<br /><br />it is actually inserting 18 rows in the emp table instead of 3.<br />Please help!Anonymoushttps://www.blogger.com/profile/06363550785138787755noreply@blogger.comtag:blogger.com,1999:blog-494502301850787022.post-61610170875113028532013-07-31T06:31:11.287-07:002013-07-31T06:31:11.287-07:00i have created table record
as
201,ashu,sharma,500...i have created table record<br />as<br />201,ashu,sharma,500<br />202,channi,singh,600<br />203,mukesh,gupta,700<br />204,arun,mehta,800<br />when i used <br />INSERT OVERWRITE TABLE record PARTITION(dated='31072013') <br />SELECT id,fname,marks, CASE WHEN id=202 THEN 'yahoo' ELSE lname END AS lname<br />FROM record;<br />then it will return<br /><br />201 sharma ashu 500 31072013<br />202 singh facebook yahoo 31072013<br />203 gupta mukesh 700 31072013<br />204 mehta arun 800 31072013<br />lastname yahoo has appeared in marks column <br />this error again and again dnt know wht to do<br />any help would be appreciated.Anonymoushttps://www.blogger.com/profile/10708541631653924296noreply@blogger.comtag:blogger.com,1999:blog-494502301850787022.post-58733507686351992982013-07-31T06:22:31.484-07:002013-07-31T06:22:31.484-07:00hi everyone the query mentioned above for updation...hi everyone the query mentioned above for updation <br />INSERT OVERWRITE TABLE employee SELECT employeeId,employeeName, experienceMonths ,salary, CASE WHEN experienceMonths >=36 THEN ‘YES’ ELSE visaEligibility END AS visaEligibility FROM employee;<br /><br />is working for one column that is you can say if record have only one column id and name<br />but when more than two column it creates problem like column format changed updation in name appeared in marks etc everytime<br />dnt know what to do..Anonymoushttps://www.blogger.com/profile/10708541631653924296noreply@blogger.comtag:blogger.com,1999:blog-494502301850787022.post-21723790873413010772013-04-01T19:41:01.557-07:002013-04-01T19:41:01.557-07:00This comment has been removed by the author.Anonymoushttps://www.blogger.com/profile/16598723382266864627noreply@blogger.comtag:blogger.com,1999:blog-494502301850787022.post-71299851813755765372013-03-25T06:09:58.709-07:002013-03-25T06:09:58.709-07:00Hi
I assume You are using hive. You can fire hive...Hi<br /><br />I assume You are using hive. You can fire hive queries as<br />1) enter into hive CLI and give your query<br />2) if query is on a file execute as hive -f fileName<br /><br />If from a java application use hive jdbc connectorBejoy KShttps://www.blogger.com/profile/03581597227249520580noreply@blogger.comtag:blogger.com,1999:blog-494502301850787022.post-50381552434924025102013-01-22T21:50:44.892-08:002013-01-22T21:50:44.892-08:00Hi Bejoy,
I am using hadoop database. I need to u...Hi Bejoy,<br /><br />I am using hadoop database. I need to update the table in hadoop database using SQL commands in hive.. <br /><br />something like : hive -e "SQL Command". Can you give me an example for that.<br />Freaky-Biologisthttps://www.blogger.com/profile/07264622416495530473noreply@blogger.comtag:blogger.com,1999:blog-494502301850787022.post-1295195558543593222012-06-20T08:10:21.755-07:002012-06-20T08:10:21.755-07:00Hi,
It is possible but the operation is expensive...Hi,<br /><br />It is possible but the operation is expensive as you are kind of overwriting the whole underlying data set. <br /><br />Do a join between the distinct data sets(tables) and insert overwrite into the required table like mentioned in the blog post. <br /><br />The join also can turn a little expensive here, as if you use a ON clause then the records will be filtered,ie only the matching records will be in output. So you may have yo go in for a full Cartesian join.<br /><br />I'm not a SQL expert, just putting in my 2 cents for you to take it ahead.Bejoy KShttps://www.blogger.com/profile/03581597227249520580noreply@blogger.comtag:blogger.com,1999:blog-494502301850787022.post-84936813318199110292012-06-20T07:40:51.092-07:002012-06-20T07:40:51.092-07:00Is there any way to do updates from another table
...Is there any way to do updates from another table<br />eg:<br /> UPDATE A <br /> FROM TEMP A <br /> ,TEMP01 B <br /> SET GUART = B.GUART <br /> WHERE A.NO = B.ANO;<br /><br />thanksavinashyelurihttps://www.blogger.com/profile/06619930447696796503noreply@blogger.com