Bigdata, Hadoop ecosystem, Semantic IoT등의 프로젝트를 진행중에 습득한 내용을 정리하는 곳입니다.
필요한 분을 위해서 공개하고 있습니다.
문의사항은 gooper@gooper.com로 메일을
보내주세요.
------ 전체 테이블 목록
select a.owner as tbl_owner, a.owner_type as tbl_owner_type,
a.tbl_name, a.tbl_type, B.DB_LOCATION_URI, b.name as db_name from HIVE.TBLS a, HIVE.DBS b where a.db_id=B.DB_id;
------전체 코디네이터 목록
select a.id, a.name, dbms_lob.substr(a.description, 10000,1), B.USERNAME from HUE.DESKTOP_DOCUMENT2 a, hue.auth_user b
where a.owner_id=B.ID and type='oozie-coordinator2' and is_history=0 and is_trashed=0;
------전체 WF 목록
select a.name, dbms_lob.substr(a.description, 10000,1), B.USERNAME from HUE.DESKTOP_DOCUMENT2 a, hue.auth_user b
where a.owner_id=B.ID and type='oozie-workflow2' and is_history=0 and is_trashed=0;
---전체 코디네이터및 WF목록
select a.id, a.name, dbms_lob.substr(a.description, 10000,1), B.USERNAME, a.type, A.LAST_MODIFIED from HUE.DESKTOP_DOCUMENT2 a, hue.auth_user b
where a.owner_id=B.ID and type in('oozie-workflow2','oozie-coordinator2') and is_history=0 and is_trashed=0;
-----from_document2_id기준 전체
select b.lvl, a.id, (select k.username from hue.auth_user k where k.id=a.owner_id) as username, a.last_modified,
a.name, dbms_lob.substr(a.description, 10000,1) as remark, a.type
, (select c.name from hue.desktop_document2 c where c.id=b.from_document2_id) as from_work, b.from_document2_id as from_id
, (select d.name from hue.desktop_document2 d where d.id=b.to_document2_id) as to_work
, (select dbms_lob.substr(e.description, 10000,1) from hue.desktop_document2 e where e.id=b.to_document2_id) as to_work_remark
, b.to_document2_id as to_id
,b.isloop
from hue.desktop_document2 a,
(
select level as lvl, id, from_document2_id, to_document2_id, connect_by_iscycle isloop from HUE.DESKTOP_DOCUMENT2_DEPENDENCIES
-- start with from_document2_id=67292 -- 코디네이터
connect by nocycle prior to_document2_id = from_document2_id
) b
where a.id=b.from_document2_id and is_history=0 and is_trashed=0 and type in ('oozie-coordinator2','oozie-workflow2');
-------코디네이터를 기준으로 코디네이터와 WF 구조 목록.
select b.lvl, a.id, (select k.username from hue.auth_user k where k.id=a.owner_id) as username, a.last_modified,
a.name, dbms_lob.substr(a.description, 10000,1) as remark, a.type
, (select c.name from hue.desktop_document2 c where c.id=b.from_document2_id) as from_work, b.from_document2_id as from_id
, (select d.name from hue.desktop_document2 d where d.id=b.to_document2_id) as to_work
, (select dbms_lob.substr(e.description, 10000,1) from hue.desktop_document2 e where e.id=b.to_document2_id) as to_work_remark
, b.to_document2_id as to_id
,b.isloop
from hue.desktop_document2 a,
(
select level as lvl, id, from_document2_id, to_document2_id, connect_by_iscycle isloop from HUE.DESKTOP_DOCUMENT2_DEPENDENCIES
-- start with from_document2_id=67292 -- 코디네이터
start with from_document2_id in (select id from HUE.DESKTOP_DOCUMENT2
where type='oozie-coordinator2' and is_history=0 and is_trashed=0)
connect by nocycle prior to_document2_id = from_document2_id
) b
where a.id=b.from_document2_id and is_history=0 and is_trashed=0 and type in ('oozie-coordinator2','oozie-workflow2');
------HUE.DESKTOP_DOCUMENT2_DEPENDENCIES에는 없고 HUE.DESKTOP_DOCUMENT2에만 있는 코디네이터 혹은 워크플로우 목록
select a.*, b.username from HUE.DESKTOP_DOCUMENT2 a, hue.auth_user b
where type in('oozie-coordinator2','oozie-workflow2') and is_history=0 and is_trashed=0
and a.id not in ( select from_document2_id from HUE.DESKTOP_DOCUMENT2_DEPENDENCIES
union
select to_document2_id from HUE.DESKTOP_DOCUMENT2_DEPENDENCIES
)
and a.owner_id=B.ID;