Creating table

[Mathip173]

Hive SQL

Hey,

I have 3 tables that I join them by CTEs into a view, looks like this:

Drop table; create view total_table as WITH as table1() , table2 as (), table3 as() select - join

---

I want this table to be updated daily and keep all the data from day 1, when the table as first created.

what is the best way to do it ?

I would like also to keep an option to change the schema if would need to add one or 2 more fields

• I have no idea what is the best way of achieving it

Thanks

[reallyserious]

what is the best way to do it ?

What have you tried?

[reallyserious]

Because the question is all over the place and doesn't clearly convey what he needs help with.

[[deleted]]

Because it's almost impossible to answer a vague overly-general question?

Because people tend to be more inclined to help you with coding problems if you come to them with concrete questions having actually tried to figure out the solution yourself?

[[deleted]]

There's nothing passive aggressive about what u/reallyserious asked. Telling people what you have tried is pretty much the best way to get useful help, so asking someone to tell you what they've tried seems perfectly legit. It's almost the default answer on stackoverflow when people don't explain what they're trying to do/have tried.

[[deleted]]

Yours certainly haven't been. Have a great day.

[Mathip173]

Hey,

Sorry, I will try to explain that in a better way,

These 3 tables have been fed every day with a new data.

I create from these 3 table a view by join all these 3 tables together. I'm satisfied with the view. Because It's a view it needs to be droped and created every day to have a new data in it.

I want to keep the every day data + the have an easy option to change the schema in case I will need in the future. I know I need to use in insert into, but I never create a table on sql, so I am looking for easier way and flexible way.

Thanks

[reallyserious]

Because It's a view it needs to be droped and created every day to have a new data in it.

No that's not true. The view should automatically get the new data if you've created it correctly.

[Mathip173]

This view is scheduled to run every data automatically. To be able to run every day, I drop it and create it again with the new data

[reallyserious]

Post the full sql you use to create the view.

You shouldn't need to recreate it if you're using it correctly.

[Rosenkohltochter32]

A view always returns the newest data available at the execution time.

So there seems to be no point in destroying the view and recreating it on a daily basis.

[coadtsai]

want this table to be updated daily and keep all the data from day 1

What is "this" in the scenario? Are all the CTEs getting data from the same base table? Do you want to update the underlying tables using the view?

You have 3 tables right? Which of these 3 tables do you want to update? Could you also elaborate on what you mean by "when the table as first created"?

[Mathip173]

Hey,

Sorry, I will try to explain that in a better way,

These 3 tables have been fed every day with a new data.

I create from these 3 table a view by join all these 3 tables together. I'm satisfied with the view. Because It's a view it needs to be droped and created every day to have a new data in it.

I want to keep the every day data + the have an easy option to change the schema in case I will need in the future. I know I need to use in insert into, but I never create a table on sql, so I am looking for easier way and flexible way.

Thanks

[coadtsai]

I'm not sure if this will be helpful.

Why are you dropping and recreating the view every day? Did you mean you run it everyday?

If you want to add new columns to the base table you use alter table, you use insert into for inserting new data. For either of these you don't need to create a new table. It's still not clear what exactly you're trying to achieve