问:
Query taking too long to execute and a unbelievable cost
I am currently writing a query and I ran explain to check the query cost and time taken to run it. I observed that the cost is too high after I ran explain. Please find the below query.

```<code>create table l1.dm_sc_temp_pl_activity_p as
select rel,licamt,tcamt, spsc, pdn, pmn4, pcs, clsldt, pci, cpdt, orgtin, bt, pcpldt, slc, sps, pm3, "ln", idt, ddt, rps, df, dat, pz, rpn, pih, pipid, pcpn, psa2, ingdt, incid, uqt,
acoid||orgid  as orgid, pn, fn, pc, pm5, pm4, rpnpi, pfdt, sfn, catp, pdcs, pm1, st, pl, cltc, cpat, incn, gn, adjsn, clsfdt, acoid, sstp, orgn, sln, sid, drg, rcdt, tel3, pm2, rpsc, pmn3,
acoid||orgid||orgrid as orgrid, alat, inpn, tel2, drgn, pcpidt, acon, pcpid, id, pcpslid, pd, dob, psa1, orgrn, pcpslidt, pcy, pcpsln, pmn2, pmn5, cid, inpid, adjs, pmn1, eldt, empi, pcpfdt, cltp, tel1, pldt, pstn, spn, spnpi, mn, clid,
efdt, capdt,ssa1,ssa2,sci,sstn,scy,sz,ftsa1,ftstn,ftz,ftci,ad1,ad2,ad3,ad4,ad5,
ad6,ad7,ad8,ad9,ad10,ad11,ad12,ad13,ad14,ad15,ad16,ad17,ad18,ad19,ad20,ad21,ad22,ad23,ad24,ad25,ad26,ad27,ad28,ad29,
adn1,adn2,adn3,adn4,adn5,adn6,adn7,adn8,adn9,adn10,adn11,adn12,adn13,adn14,
adn15,adn16,adn17,adn18,adn19,adn20,adn21,adn22,adn23,adn24,adn25,adn26,adn27,adn28,adn29
from (
select
 c.prov_addr1 as ftsa1,
c.prov_state as ftstn,
c.prov_zip as ftz,
c.prov_city as ftci,
c.iscap as rel,
cast(c.claimsvc_billedamt as decimal(18,2)) as licamt,
cast(c.claim_billedamt as decimal(18,2))  as tcamt,
trim(m.id) as id ,trim(m.tel1) as tel1,trim(m.tel2) as tel2,trim(m.tel3)as tel3,
'' as empi,trim(m.gn) as gn,psa1,psa2,pci,pcpldt,pl,trim(mn) as mn,trim(pz) as pz,trim(ln) as ln,
cast(m.dob as date) as dob,'N' as df,m.pcy,trim(m.fn) as fn,m.pcpfdt,m.pstn,
ssa1,ssa2,sci,sstn,scy,trim(sz) as sz,
--dm_prov
case when (trim(p.npi) in ('NULL','NA','')) then '' else trim(p.npi) end as pcpid,
case when (trim(p.npi) in ('NULL','NA','')) then '' else 'NPI' end as pcpidt,
p.lastname||','||p.firstname||' '||p.middlename as pcpn,
-- dm_vend
case when (trim(v.taxid) in ('NULL','NA','')) then '' else trim(v.taxid) end as pcpslid,
v.vendname as pcpsln,
case when (trim(v.taxid) in ('NULL','NA','')) then '' else 'NPI'  end as pcpslidt,
-- level
case when l."HPName"='Aetna Health of California' then '13'
when l."HPName"='Health Net' then '14'
when l."HPName"='Blue Shield of California' then '12'
when l."HPName"='United Healthcare of California' then '15'
when l."HPName"='Cigna for Health Care Professionals' then '16'
when l."HPName"='Anthem Blue Cross' then '17' end as incid,
l."HPName" as incn,
Case
when l."Progdescr"='DHMNSC United Healthcare Commercial' then '30'
when l."Progdescr"='DHMNSC Blue Shield 65 Plus' then '29'
when l."Progdescr"='DHMNSC United Healthcare Canopy Commercial' then '31'
when l."Progdescr"='DHMNSC United Healthcare Medicare Solutions' then '32'
when l."Progdescr"='DHMNSC Blue Shield Commercial' then '28'
when l."Progdescr"  in ('DHMNSC Health Net SmartCare Commercial','DHMNSC Health Net Canopy UC Blue and Gold Commercial',
'DHMNSC Health Net Canopy Care Commercial') then '33'
when l."Progdescr"='DHMNSC Aetna Commercial' then '34'
when l."Progdescr"='DHMNSC Cigna Commercial' then '35'
when l."Progdescr"='DHMNSC Health Net Commercial' then '36'
when l."Progdescr"='DHMNSC Anthem Blue Cross Commercial' then '37'
when l."Progdescr"='DHMNSC Health Net Seniority Plus' then '38'
when l."Progdescr"='DHMNSC United Healthcare Canopy Medicare Advantage' then '50' end as inpid,
case when l."Progdescr"  in ('DHMNSC Health Net SmartCare Commercial','DHMNSC Health Net Canopy UC Blue and Gold Commercial',
'DHMNSC Health Net Canopy Care Commercial') then 'DHMNSC Health Net SmartCare Commercial'
else l."Progdescr"  end as inpn,
'3' as acoid,
'DHMN-SC' as acon,
case when l."LOB" ='M' then '6'
    when l."LOB" ='C' then '7' else '8' end as orgid,
case when l."LOB"='C' then 'Commercial'when l."LOB"='M' then 'Medicare' else "LOB" end as orgn,
case when  l."HPCode" not in ('PGM000295','PGM000294','PGM000333','PGM000332','PGM000326') then '7'
     when l."HPCode" in ('PGM000295','PGM000294','PGM000333','PGM000332','PGM000326') then '9' else '8' end as orgrid,
case when  l."HPCode" not in ('PGM000295','PGM000294','PGM000333','PGM000332','PGM000326') then 'Non Canopy'
     when l."HPCode" in ('PGM000295','PGM000294','PGM000333','PGM000332','PGM000326') then 'Canopy' else 'Other MedGroup' end as orgrn,
-- hardcode columns
'3a1bcd23-d88c-4c70-8ffa-267673232647' as pipid,
'Claims' as st,
l."HPName" as sstp,
'DataMart.bak' as sfn,
current_date as rcdt,
current_date as ingdt,
'claimline' as cltp,
'5' as sid,
'Patient ID' as idt,
 -- dm_claim
trim(c.claimno) as cid,
trim(c.seq) as clid,
'Professional' as cltc,
case when (trim(c.billtype) in ('','NULL','NA') or trim(c.billtype) is null)
    then '' else trim(c.billtype) end as bt,
case when (trim(c.claim_fromdate) in ('','NULL','NA'))then null
    else cast(c.claim_fromdate as date) end as efdt,
case when (trim(c.claim_todate) in ('','NULL','NA'))then null
    else cast(c.claim_todate as date) end as eldt,
case when (trim(c.claim_recddate) in ('','NULL','NA'))then null
    else cast(c.claim_recddate as date) end as cpdt,
case when (trim(c.claim_paiddate) in ('','NULL','NA'))then null
    else cast(c.claim_paiddate as date) end as capdt,
case when (c.claim_netamt::decimal(18,2)='0' and c.iscap='1') then cast(c.claim_contramt as decimal(18,2))
else cast(c.claim_netamt as decimal(18,2))  end as pih,
case when trim(c.claimno)  like '%A1' then 'A'
     when trim(c.claimno)  like '%A2' then 'A'
     when trim(c.claimno)  like '%A3' then 'A'
    when trim(c.claimno)  like '%A4' then 'A'
     when trim(c.claimno)  like '%R1' then 'C'
     when trim(c.claimno)  like '%R2' then 'C'
     when trim(c.claimno)  like '%R3' then 'C'
     when trim(c.claimno)  like '%R4' then 'C'
     when trim(c.claimno)  like '%R5' then 'C' else 'O' end as adjs,
case when trim(c.claimno)  like '%A1' then 'Adjusted'
     when trim(c.claimno)  like '%A2' then 'Adjusted'
     when trim(c.claimno)  like '%A3' then 'Adjusted'
    when trim(c.claimno)  like '%A4' then 'Adjusted'
     when trim(c.claimno)  like '%R1' then 'Cancelled'
     when trim(c.claimno)  like '%R2' then 'Cancelled'
     when trim(c.claimno)  like '%R3' then 'Cancelled'
     when trim(c.claimno)  like '%R4' then 'Cancelled'
     when trim(c.claimno)  like '%R5' then 'Cancelled' else 'Original' end as adjsn,
case when (trim(c.taxid) in ('','NULL','NA') or trim(c.taxid) is null)
    then '' else trim(c.taxid) end as orgtin,
case when (trim(c.npi ) in ('','NULL','NA') or trim(c.npi) is null)
    then '' else trim(c.npi) end as spnpi,
case when (c.prov_name in ('','NULL','NA') or c.prov_name is null) then p1.name else c.prov_name end as spn,
p1.ptxcd as spsc,
p1.psn as sps,
case when (trim(c.drgcode ) in ('','NULL','NA') or trim(c.drgcode) is null)
    then '' else trim(c.drgcode) end as drg,
c.drg_descr as drgn,
case when (trim(c.claimsvc_fromdate) in ('','NULL','NA'))then null
    else cast(c.claimsvc_fromdate as date) end as clsfdt,
case when (trim(c.claimsvc_todate) in ('','NULL','NA'))then null
    else cast(c.claimsvc_todate as date) end  as clsldt,
case when (trim(c.claimsvc_qty ) in ('','NULL','NA') or trim(c.claimsvc_qty) is null)
    then '' else trim(c.claimsvc_qty) end as uqt,
--cast(c.claimsvc_netamt as decimal(18,2)) as catp,
case when c.iscap='1' then cast(c.claimsvc_contramt as decimal(18,2)) else cast(c.claimsvc_netamt as decimal(18,2)) end as catp,
cast(c.claimsvc_allowedamt as decimal(18,2)) as alat,
cast(c.claimsvc_copayamt as decimal(18,2)) as cpat,
cast(c.claimsvc_deductamt as decimal(18,2)) as dat,
case when (trim(c.svccode ) in ('','NULL','NA') or trim(c.svccode) is null)
    then '' else trim(c.svccode) end as pc,
case when (trim(c.svc_longdescr ) in ('','NULL','NA') or trim(c.svc_longdescr) is null)
    then '' else trim(c.svc_longdescr) end as pn,
    ont.csys as pcs,
case when (trim(c.claimsvc_fromdate) in ('','NULL','NA'))then null
    else cast(c.claimsvc_fromdate as date) end as pfdt,
case when (trim(c.claimsvc_todate) in ('','NULL','NA'))then null
    else cast(c.claimsvc_todate as date) end  as pldt,
mf.pm1 ,
mf.pm2 ,
mf.pm3,
mf.pm4,
mf.pm5 ,
mf.pmn1,
mf.pmn2 ,
mf.pmn3,
mf.pmn4,
mf.pmn5,
case when c.poscode in('01','1') then 'Pharmacy'
when c.poscode in ('02','2') then 'Telehealth'
when c.poscode in('03','3') then 'School'
when c.poscode in ('04','4') then 'Homeless Shelter'
when c.poscode in ('05','5') then 'Indian Health Service Free-standing Facility'
when c.poscode='06' then 'Indian Health Service Provider-based Facility'
when c.poscode in ('07','7') then 'Tribal 638 Free-standing Facility'
when c.poscode in ('08','8') then 'Tribal 638 Provider-based Facility'
when c.poscode in('09','9') then 'Prison/Correctional Facility'
when c.poscode in ('10','77') then 'Unassigned'
when c.poscode='11' then 'Office'
when c.poscode='12' then 'Home'
when c.poscode='13' then 'Assisted Living Facility'
when c.poscode='14' then 'Group Home'
when c.poscode='15' then 'Mobile Unit'
when c.poscode='16' then 'Temporary Lodging'
when c.poscode='17' then 'Walk-in Retail Health Clinic'
when c.poscode='18' then 'Place of Employment-Worksite'
when c.poscode='19' then 'Off Campus-Outpatient Hospital'
when c.poscode='20' then 'Urgent Care facility'
when c.poscode='21' then 'Inpatient Hospital'
when c.poscode='22' then 'On Campus-Outpatient Hospital'
when c.poscode='23' then 'Emergency Room – Hospital'
when c.poscode in ('24','B') then 'Ambulatory Surgical Center'
when c.poscode='25' then 'Birthing Center'
when c.poscode='26' then 'Military Treatment Facility'
when c.poscode='31' then 'Skilled Nursing Facility'
when c.poscode='32' then 'Nursing Facility'
when c.poscode='33' then 'Custodial Care Facility'
when c.poscode='34' then 'Hospice'
when c.poscode='41' then 'Ambulance - Land'
when c.poscode='42' then 'Ambulance – Air or Water'
when c.poscode='49' then 'Independent Clinic'
when c.poscode='50' then 'Federally Qualified Health Center'
when c.poscode='51' then 'Inpatient Psychiatric Facility'
when c.poscode='52' then 'Psychiatric Facility-Partial Hospitalization'
when c.poscode='53' then 'Community Mental Health Center'
when c.poscode='54' then 'Intermediate Care Facility/ Individuals with Intellectual Disabilities'
when c.poscode='55' then 'Residential Substance Abuse Treatment Facility'
when c.poscode='56' then 'Psychiatric Residential Treatment Center'
when c.poscode='57' then 'Non-residential Substance Abuse Treatment Facility'
when c.poscode='60' then 'Mass Immunization Center'
when c.poscode='61' then 'Comprehensive Inpatient Rehabilitation Facility'
when c.poscode='62' then 'Comprehensive Outpatient Rehabilitation Facility'
when c.poscode='65' then 'End-Stage Renal Disease Treatment Facility'
when c.poscode='71' then 'Public Health Clinic'
when c.poscode='72' then 'Rural Health Clinic'
when c.poscode in ('81','A') then 'Independent Laboratory'
when c.poscode in ('99','?','O') then 'Other Place of Service' end as sln,
case when c.poscode in ('O','?') then '99'
when c.poscode='A' then '81'
when c.poscode='B' then '24'
when c.poscode in('1','2','3','4','5','6','7','8','9') then '0'||c.poscode else c.poscode end slc,
case when (trim(c.pd ) in ('','NULL','NA') or trim(c.pd ) is null)
    then '' else trim(c.pd) end as pd,
case when (trim(c.pdn ) in ('','NULL','NA') or trim(c.pdn) is null)
    then '' else trim(c.pdn) end as pdn,
pdcs as pdcs,
case when (trim(c.pd ) in ('','NULL','NA') or trim(c.pd ) is null)
    then Null else (
    case when (trim(c.claimsvc_fromdate) in ('','NULL','NA'))then null
    else cast(c.claimsvc_fromdate as date) end )end as ddt,
case when (trim(c.ref_npi ) in ('','NULL','NA') or trim(c.ref_npi) is null)
    then '' else trim(c.ref_npi) end as rpnpi,
    p2."name" as rpn,
p2.ptxcd as rpsc,
p2.psn as rps,
ad1,ad2,ad3,ad4,ad5,
ad6,ad7,ad8,ad9,ad10,ad11,ad12,ad13,ad14,ad15,ad16,ad17,ad18,ad19,ad20,ad21,ad22,ad23,ad24,ad25,ad26,ad27,ad28,ad29,
adn1,adn2,adn3,adn4,adn5,adn6,adn7,adn8,adn9,adn10,adn11,adn12,adn13,adn14,
adn15,adn16,adn17,adn18,adn19,adn20,adn21,adn22,adn23,adn24,adn25,adn26,adn27,adn28,adn29
from (select * from l1.dm_proc_memb
where hpcode in ('PGM000333','PGM000332','PGM000326','PGM000268', 'PGM000269', 'PGM000270', 'PGM000272', 'PGM000273', 'PGM000274', 'PGM000275', 'PGM000276', 'PGM000294', 'PGM000295')) m
inner join l1.dm_proc_claim_p c
on m.memb_id=c.claim_memb
left join l1.dm_proc_modif mf
on mf.claimsvc_id=c.claimsvc_id
left join l1."dm_prov" p on p.prov_id =m.prov_id
left join l1."dm_vend" v on v.vend_id =m.vend_id
left join (select * from l1."level" where "HPCode" in ('PGM000333','PGM000332','PGM000326','PGM000268', 'PGM000269', 'PGM000270', 'PGM000272', 'PGM000273', 'PGM000274', 'PGM000275', 'PGM000276', 'PGM000294', 'PGM000295')) l
on trim(l."HPCode" )=trim(m.hpcode)
left join l2.pd_npi p1 on trim(c.npi)=trim(p1.npi)
left join (select * from l2.pd_ontology where dtp ='procedure' )ont
on trim(ont.cval) =trim(c.svccode )
--left join l2.pd_ontology ontd
--on trim(ontd.cval) =trim(c.pd) and ontd.dtp ='diagnosis'
left join l2.pd_npi p2 on trim(c.ref_npi )=trim(p2.npi)
)f
</code>```

I ran explain command onto this query and I observed the following op:

```<code>  Merge Cond: ((btrim((p2.npi)::text)) = (btrim((c.ref_npi)::text)))
  ->  Sort  (cost=1761851.74..1785989.13 rows=9654958 width=108)
        Sort Key: (btrim((p2.npi)::text))
        ->  Seq Scan on pd_npi p2  (cost=0.00..641739.58 rows=9654958 width=108)
  ->  Materialize  (cost=159139479153552696279040.00..159226719441637676679168.00 rows=17448057616994486190080 width=3383)
        ->  Sort  (cost=159139479153552696279040.00..159183099297595186479104.00 rows=17448057616994486190080 width=3383)
              Sort Key: (btrim((c.ref_npi)::text))
              ->  Merge Join  (cost=456947875487.57..261731712177051860992.00 rows=17448057616994486190080 width=3383)
                    Merge Cond: ((c.claim_memb)::text = (dm_proc_memb.memb_id)::text)
                    ->  Nested Loop Left Join  (cost=157761.50..10839427546273626.00 rows=2707460197662070 width=2450)
                          Join Filter: (btrim((c.npi)::text) = btrim((p1.npi)::text))
                          ->  Nested Loop Left Join  (cost=157761.50..9586754880584.63 rows=56084349568 width=2353)
                                Join Filter: (btrim((pd_ontology.cval)::text) = btrim((c.svccode)::text))
                                ->  Nested Loop Left Join  (cost=0.56..9362416722695.26 rows=43719408 width=2348)
                                      Join Filter: ((mf.claimsvc_id)::text = (c.claimsvc_id)::text)
                                      ->  Index Scan Backward using l1_dm_proc_claims_p_id_idx on dm_proc_claim_p c  (cost=0.56..17080655.54 rows=43719408 width=2036)
                                      ->  Materialize  (cost=0.00..376215.44 rows=14276496 width=352)
                                            ->  Seq Scan on dm_proc_modif mf  (cost=0.00..304832.96 rows=14276496 width=352)
                                ->  Materialize  (cost=157760.93..760260.39 rows=256565 width=14)
                                      ->  Bitmap Heap Scan on pd_ontology  (cost=157760.93..758977.56 rows=256565 width=14)
                                            Recheck Cond: ((dtp)::text = 'procedure'::text)
                                            ->  Bitmap Index Scan on pd_ont_idx_dtp  (cost=0.00..157696.79 rows=256565 width=0)
                                                  Index Cond: ((dtp)::text = 'procedure'::text)
                          ->  Materialize  (cost=0.00..690014.37 rows=9654958 width=108)
                                ->  Seq Scan on pd_npi p1  (cost=0.00..641739.58 rows=9654958 width=108)
                    ->  Materialize  (cost=456947717726.07..459097227591.84 rows=429901973154 width=972)
                          ->  Sort  (cost=456947717726.07..458022472658.96 rows=429901973154 width=972)
                                Sort Key: dm_proc_memb.memb_id NULLS FIRST
                                ->  Merge Right Join  (cost=82668031.11..6531565545.79 rows=429901973154 width=972)
                                      Merge Cond: ((v.vend_id)::text = (dm_proc_memb.vend_id)::text)
                                      ->  Sort  (cost=88160.95..89633.33 rows=588951 width=96)
                                            Sort Key: v.vend_id
                                            ->  Seq Scan on dm_vend v  (cost=0.00..31716.51 rows=588951 width=96)
                                      ->  Materialize  (cost=82579870.16..83309815.39 rows=145989046 width=940)
                                            ->  Sort  (cost=82579870.16..82944842.77 rows=145989046 width=940)
                                                  Sort Key: dm_proc_memb.vend_id
                                                  ->  Merge Right Join  (cost=165139.84..2405511.13 rows=145989046 width=940)
                                                        Merge Cond: ((p.prov_id)::text = (dm_proc_memb.prov_id)::text)
                                                        ->  Index Scan using dm_prov_prov_id on dm_prov p  (cost=0.42..49937.79 rows=239291 width=160)
                                                        ->  Sort  (cost=165139.42..165444.47 rows=122018 width=844)
                                                              Sort Key: dm_proc_memb.prov_id
                                                              ->  Hash Left Join  (cost=485.00..154830.89 rows=122018 width=844)
                                                                    Hash Cond: (btrim((dm_proc_memb.hpcode)::text) = btrim((level."HPCode")::text))
                                                                    ->  Bitmap Heap Scan on dm_proc_memb  (cost=481.71..153985.68 rows=122018 width=812)
                                                                          Recheck Cond: ((hpcode)::text = ANY ('{PGM000333,PGM000332,PGM000326,PGM000268,PGM000269,PGM000270,PGM000272,PGM000273,PGM000274,PGM000275,PGM000276,PGM000294,PGM000295}'::text[]))
                                                                          ->  Bitmap Index Scan on l1_proc_memb_id_idx  (cost=0.00..451.20 rows=122018 width=0)
                                                                    ->  Hash  (cost=3.13..3.13 rows=13 width=64)
                                                                          ->  Seq Scan on level  (cost=0.00..3.13 rows=13 width=64)
                                                                                Filter: (("HPCode")::text = ANY ('{PGM000333,PGM000332,PGM000326,PGM000268,PGM000269,PGM000270,PGM000272,PGM000273,PGM000274,PGM000275,PGM000276,PGM000294,PGM000295}'::text[]))
</code>```

The cost taken by the query is too much. How can I reduce the cost and save time.
P.S I analyzed and found that left joins are increasing the cost. Please tell me how to manage this.

The max size of the tables is 32 GB.

Thanks!!

答:
That's a big query, possibly too big for StackOverflow volunteers to wrap our heads around. I do notice one thing, though: you have multiple occurrences of

```<code>left join t on trim(t.col) = trim(a.col)   /* slow! */
</code>```

That's a query-performance antipattern. If you want good performance you should go to great lengths to avoid calling functions on column values in WHERE and ON clauses. You want all columns in those clauses to be sargable. You can call functions on constant values, though.

Your objective should be to have that LEFT JOIN read:

```<code>left join t on t.col = a.col
</code>```

And you should be sure the two sides of ON clauses have precisely the same data type; that makes it easier for the database server to do the joins efficiently.

Your monster query seems to have a presentation layer with a long SELECT clause with many complex column definitions. Then, in its last 15 lines or so, it has a data-retrieval subquery starting with (select..... If you're trying to optimize the query, you'll be wise to focus on the data-retrieval subquery, stripping away the presentation layer.

Once you make your ON and WHERE clauses sargable, you can move on to looking at your tables' indexes.

Body:
I am currently writing a query and I ran explain to check the query cost and time taken to run it. I observed that the cost is too high after I ran explain. Please find the below query.

create table l1.dm_sc_temp_pl_activity_p as
select rel,licamt,tcamt, spsc, pdn, pmn4, pcs, clsldt, pci, cpdt, orgtin, bt, pcpldt, slc, sps, pm3, "ln", idt, ddt, rps, df, dat, pz, rpn, pih, pipid, pcpn, psa2, ingdt, incid, uqt,
acoid||orgid  as orgid, pn, fn, pc, pm5, pm4, rpnpi, pfdt, sfn, catp, pdcs, pm1, st, pl, cltc, cpat, incn, gn, adjsn, clsfdt, acoid, sstp, orgn, sln, sid, drg, rcdt, tel3, pm2, rpsc, pmn3,
acoid||orgid||orgrid as orgrid, alat, inpn, tel2, drgn, pcpidt, acon, pcpid, id, pcpslid, pd, dob, psa1, orgrn, pcpslidt, pcy, pcpsln, pmn2, pmn5, cid, inpid, adjs, pmn1, eldt, empi, pcpfdt, cltp, tel1, pldt, pstn, spn, spnpi, mn, clid,
efdt, capdt,ssa1,ssa2,sci,sstn,scy,sz,ftsa1,ftstn,ftz,ftci,ad1,ad2,ad3,ad4,ad5,
ad6,ad7,ad8,ad9,ad10,ad11,ad12,ad13,ad14,ad15,ad16,ad17,ad18,ad19,ad20,ad21,ad22,ad23,ad24,ad25,ad26,ad27,ad28,ad29,
adn1,adn2,adn3,adn4,adn5,adn6,adn7,adn8,adn9,adn10,adn11,adn12,adn13,adn14,
adn15,adn16,adn17,adn18,adn19,adn20,adn21,adn22,adn23,adn24,adn25,adn26,adn27,adn28,adn29
from (
select
 c.prov_addr1 as ftsa1,
c.prov_state as ftstn,
c.prov_zip as ftz,
c.prov_city as ftci,
c.iscap as rel,
cast(c.claimsvc_billedamt as decimal(18,2)) as licamt,
cast(c.claim_billedamt as decimal(18,2))  as tcamt,
trim(m.id) as id ,trim(m.tel1) as tel1,trim(m.tel2) as tel2,trim(m.tel3)as tel3,
'' as empi,trim(m.gn) as gn,psa1,psa2,pci,pcpldt,pl,trim(mn) as mn,trim(pz) as pz,trim(ln) as ln,
cast(m.dob as date) as dob,'N' as df,m.pcy,trim(m.fn) as fn,m.pcpfdt,m.pstn,
ssa1,ssa2,sci,sstn,scy,trim(sz) as sz,
--dm_prov
case when (trim(p.npi) in ('NULL','NA','')) then '' else trim(p.npi) end as pcpid,
case when (trim(p.npi) in ('NULL','NA','')) then '' else 'NPI' end as pcpidt,
p.lastname||','||p.firstname||' '||p.middlename as pcpn,
-- dm_vend
case when (trim(v.taxid) in ('NULL','NA','')) then '' else trim(v.taxid) end as pcpslid,
v.vendname as pcpsln,
case when (trim(v.taxid) in ('NULL','NA','')) then '' else 'NPI'  end as pcpslidt,
-- level
case when l."HPName"='Aetna Health of California' then '13'
when l."HPName"='Health Net' then '14'
when l."HPName"='Blue Shield of California' then '12'
when l."HPName"='United Healthcare of California' then '15'
when l."HPName"='Cigna for Health Care Professionals' then '16'
when l."HPName"='Anthem Blue Cross' then '17' end as incid,
l."HPName" as incn,
Case
when l."Progdescr"='DHMNSC United Healthcare Commercial' then '30'
when l."Progdescr"='DHMNSC Blue Shield 65 Plus' then '29'
when l."Progdescr"='DHMNSC United Healthcare Canopy Commercial' then '31'
when l."Progdescr"='DHMNSC United Healthcare Medicare Solutions' then '32'
when l."Progdescr"='DHMNSC Blue Shield Commercial' then '28'
when l."Progdescr"  in ('DHMNSC Health Net SmartCare Commercial','DHMNSC Health Net Canopy UC Blue and Gold Commercial',
'DHMNSC Health Net Canopy Care Commercial') then '33'
when l."Progdescr"='DHMNSC Aetna Commercial' then '34'
when l."Progdescr"='DHMNSC Cigna Commercial' then '35'
when l."Progdescr"='DHMNSC Health Net Commercial' then '36'
when l."Progdescr"='DHMNSC Anthem Blue Cross Commercial' then '37'
when l."Progdescr"='DHMNSC Health Net Seniority Plus' then '38'
when l."Progdescr"='DHMNSC United Healthcare Canopy Medicare Advantage' then '50' end as inpid,
case when l."Progdescr"  in ('DHMNSC Health Net SmartCare Commercial','DHMNSC Health Net Canopy UC Blue and Gold Commercial',
'DHMNSC Health Net Canopy Care Commercial') then 'DHMNSC Health Net SmartCare Commercial'
else l."Progdescr"  end as inpn,
'3' as acoid,
'DHMN-SC' as acon,
case when l."LOB" ='M' then '6'
    when l."LOB" ='C' then '7' else '8' end as orgid,
case when l."LOB"='C' then 'Commercial'when l."LOB"='M' then 'Medicare' else "LOB" end as orgn,
case when  l."HPCode" not in ('PGM000295','PGM000294','PGM000333','PGM000332','PGM000326') then '7'
     when l."HPCode" in ('PGM000295','PGM000294','PGM000333','PGM000332','PGM000326') then '9' else '8' end as orgrid,
case when  l."HPCode" not in ('PGM000295','PGM000294','PGM000333','PGM000332','PGM000326') then 'Non Canopy'
     when l."HPCode" in ('PGM000295','PGM000294','PGM000333','PGM000332','PGM000326') then 'Canopy' else 'Other MedGroup' end as orgrn,
-- hardcode columns
'3a1bcd23-d88c-4c70-8ffa-267673232647' as pipid,
'Claims' as st,
l."HPName" as sstp,
'DataMart.bak' as sfn,
current_date as rcdt,
current_date as ingdt,
'claimline' as cltp,
'5' as sid,
'Patient ID' as idt,
 -- dm_claim
trim(c.claimno) as cid,
trim(c.seq) as clid,
'Professional' as cltc,
case when (trim(c.billtype) in ('','NULL','NA') or trim(c.billtype) is null)
    then '' else trim(c.billtype) end as bt,
case when (trim(c.claim_fromdate) in ('','NULL','NA'))then null
    else cast(c.claim_fromdate as date) end as efdt,
case when (trim(c.claim_todate) in ('','NULL','NA'))then null
    else cast(c.claim_todate as date) end as eldt,
case when (trim(c.claim_recddate) in ('','NULL','NA'))then null
    else cast(c.claim_recddate as date) end as cpdt,
case when (trim(c.claim_paiddate) in ('','NULL','NA'))then null
    else cast(c.claim_paiddate as date) end as capdt,
case when (c.claim_netamt::decimal(18,2)='0' and c.iscap='1') then cast(c.claim_contramt as decimal(18,2))
else cast(c.claim_netamt as decimal(18,2))  end as pih,
case when trim(c.claimno)  like '%A1' then 'A'
     when trim(c.claimno)  like '%A2' then 'A'
     when trim(c.claimno)  like '%A3' then 'A'
    when trim(c.claimno)  like '%A4' then 'A'
     when trim(c.claimno)  like '%R1' then 'C'
     when trim(c.claimno)  like '%R2' then 'C'
     when trim(c.claimno)  like '%R3' then 'C'
     when trim(c.claimno)  like '%R4' then 'C'
     when trim(c.claimno)  like '%R5' then 'C' else 'O' end as adjs,
case when trim(c.claimno)  like '%A1' then 'Adjusted'
     when trim(c.claimno)  like '%A2' then 'Adjusted'
     when trim(c.claimno)  like '%A3' then 'Adjusted'
    when trim(c.claimno)  like '%A4' then 'Adjusted'
     when trim(c.claimno)  like '%R1' then 'Cancelled'
     when trim(c.claimno)  like '%R2' then 'Cancelled'
     when trim(c.claimno)  like '%R3' then 'Cancelled'
     when trim(c.claimno)  like '%R4' then 'Cancelled'
     when trim(c.claimno)  like '%R5' then 'Cancelled' else 'Original' end as adjsn,
case when (trim(c.taxid) in ('','NULL','NA') or trim(c.taxid) is null)
    then '' else trim(c.taxid) end as orgtin,
case when (trim(c.npi ) in ('','NULL','NA') or trim(c.npi) is null)
    then '' else trim(c.npi) end as spnpi,
case when (c.prov_name in ('','NULL','NA') or c.prov_name is null) then p1.name else c.prov_name end as spn,
p1.ptxcd as spsc,
p1.psn as sps,
case when (trim(c.drgcode ) in ('','NULL','NA') or trim(c.drgcode) is null)
    then '' else trim(c.drgcode) end as drg,
c.drg_descr as drgn,
case when (trim(c.claimsvc_fromdate) in ('','NULL','NA'))then null
    else cast(c.claimsvc_fromdate as date) end as clsfdt,
case when (trim(c.claimsvc_todate) in ('','NULL','NA'))then null
    else cast(c.claimsvc_todate as date) end  as clsldt,
case when (trim(c.claimsvc_qty ) in ('','NULL','NA') or trim(c.claimsvc_qty) is null)
    then '' else trim(c.claimsvc_qty) end as uqt,
--cast(c.claimsvc_netamt as decimal(18,2)) as catp,
case when c.iscap='1' then cast(c.claimsvc_contramt as decimal(18,2)) else cast(c.claimsvc_netamt as decimal(18,2)) end as catp,
cast(c.claimsvc_allowedamt as decimal(18,2)) as alat,
cast(c.claimsvc_copayamt as decimal(18,2)) as cpat,
cast(c.claimsvc_deductamt as decimal(18,2)) as dat,
case when (trim(c.svccode ) in ('','NULL','NA') or trim(c.svccode) is null)
    then '' else trim(c.svccode) end as pc,
case when (trim(c.svc_longdescr ) in ('','NULL','NA') or trim(c.svc_longdescr) is null)
    then '' else trim(c.svc_longdescr) end as pn,
    ont.csys as pcs,
case when (trim(c.claimsvc_fromdate) in ('','NULL','NA'))then null
    else cast(c.claimsvc_fromdate as date) end as pfdt,
case when (trim(c.claimsvc_todate) in ('','NULL','NA'))then null
    else cast(c.claimsvc_todate as date) end  as pldt,
mf.pm1 ,
mf.pm2 ,
mf.pm3,
mf.pm4,
mf.pm5 ,
mf.pmn1,
mf.pmn2 ,
mf.pmn3,
mf.pmn4,
mf.pmn5,
case when c.poscode in('01','1') then 'Pharmacy'
when c.poscode in ('02','2') then 'Telehealth'
when c.poscode in('03','3') then 'School'
when c.poscode in ('04','4') then 'Homeless Shelter'
when c.poscode in ('05','5') then 'Indian Health Service Free-standing Facility'
when c.poscode='06' then 'Indian Health Service Provider-based Facility'
when c.poscode in ('07','7') then 'Tribal 638 Free-standing Facility'
when c.poscode in ('08','8') then 'Tribal 638 Provider-based Facility'
when c.poscode in('09','9') then 'Prison/Correctional Facility'
when c.poscode in ('10','77') then 'Unassigned'
when c.poscode='11' then 'Office'
when c.poscode='12' then 'Home'
when c.poscode='13' then 'Assisted Living Facility'
when c.poscode='14' then 'Group Home'
when c.poscode='15' then 'Mobile Unit'
when c.poscode='16' then 'Temporary Lodging'
when c.poscode='17' then 'Walk-in Retail Health Clinic'
when c.poscode='18' then 'Place of Employment-Worksite'
when c.poscode='19' then 'Off Campus-Outpatient Hospital'
when c.poscode='20' then 'Urgent Care facility'
when c.poscode='21' then 'Inpatient Hospital'
when c.poscode='22' then 'On Campus-Outpatient Hospital'
when c.poscode='23' then 'Emergency Room – Hospital'
when c.poscode in ('24','B') then 'Ambulatory Surgical Center'
when c.poscode='25' then 'Birthing Center'
when c.poscode='26' then 'Military Treatment Facility'
when c.poscode='31' then 'Skilled Nursing Facility'
when c.poscode='32' then 'Nursing Facility'
when c.poscode='33' then 'Custodial Care Facility'
when c.poscode='34' then 'Hospice'
when c.poscode='41' then 'Ambulance - Land'
when c.poscode='42' then 'Ambulance – Air or Water'
when c.poscode='49' then 'Independent Clinic'
when c.poscode='50' then 'Federally Qualified Health Center'
when c.poscode='51' then 'Inpatient Psychiatric Facility'
when c.poscode='52' then 'Psychiatric Facility-Partial Hospitalization'
when c.poscode='53' then 'Community Mental Health Center'
when c.poscode='54' then 'Intermediate Care Facility/ Individuals with Intellectual Disabilities'
when c.poscode='55' then 'Residential Substance Abuse Treatment Facility'
when c.poscode='56' then 'Psychiatric Residential Treatment Center'
when c.poscode='57' then 'Non-residential Substance Abuse Treatment Facility'
when c.poscode='60' then 'Mass Immunization Center'
when c.poscode='61' then 'Comprehensive Inpatient Rehabilitation Facility'
when c.poscode='62' then 'Comprehensive Outpatient Rehabilitation Facility'
when c.poscode='65' then 'End-Stage Renal Disease Treatment Facility'
when c.poscode='71' then 'Public Health Clinic'
when c.poscode='72' then 'Rural Health Clinic'
when c.poscode in ('81','A') then 'Independent Laboratory'
when c.poscode in ('99','?','O') then 'Other Place of Service' end as sln,
case when c.poscode in ('O','?') then '99'
when c.poscode='A' then '81'
when c.poscode='B' then '24'
when c.poscode in('1','2','3','4','5','6','7','8','9') then '0'||c.poscode else c.poscode end slc,
case when (trim(c.pd ) in ('','NULL','NA') or trim(c.pd ) is null)
    then '' else trim(c.pd) end as pd,
case when (trim(c.pdn ) in ('','NULL','NA') or trim(c.pdn) is null)
    then '' else trim(c.pdn) end as pdn,
pdcs as pdcs,
case when (trim(c.pd ) in ('','NULL','NA') or trim(c.pd ) is null)
    then Null else (
    case when (trim(c.claimsvc_fromdate) in ('','NULL','NA'))then null
    else cast(c.claimsvc_fromdate as date) end )end as ddt,
case when (trim(c.ref_npi ) in ('','NULL','NA') or trim(c.ref_npi) is null)
    then '' else trim(c.ref_npi) end as rpnpi,
    p2."name" as rpn,
p2.ptxcd as rpsc,
p2.psn as rps,
ad1,ad2,ad3,ad4,ad5,
ad6,ad7,ad8,ad9,ad10,ad11,ad12,ad13,ad14,ad15,ad16,ad17,ad18,ad19,ad20,ad21,ad22,ad23,ad24,ad25,ad26,ad27,ad28,ad29,
adn1,adn2,adn3,adn4,adn5,adn6,adn7,adn8,adn9,adn10,adn11,adn12,adn13,adn14,
adn15,adn16,adn17,adn18,adn19,adn20,adn21,adn22,adn23,adn24,adn25,adn26,adn27,adn28,adn29
from (select * from l1.dm_proc_memb
where hpcode in ('PGM000333','PGM000332','PGM000326','PGM000268', 'PGM000269', 'PGM000270', 'PGM000272', 'PGM000273', 'PGM000274', 'PGM000275', 'PGM000276', 'PGM000294', 'PGM000295')) m
inner join l1.dm_proc_claim_p c
on m.memb_id=c.claim_memb
left join l1.dm_proc_modif mf
on mf.claimsvc_id=c.claimsvc_id
left join l1."dm_prov" p on p.prov_id =m.prov_id
left join l1."dm_vend" v on v.vend_id =m.vend_id
left join (select * from l1."level" where "HPCode" in ('PGM000333','PGM000332','PGM000326','PGM000268', 'PGM000269', 'PGM000270', 'PGM000272', 'PGM000273', 'PGM000274', 'PGM000275', 'PGM000276', 'PGM000294', 'PGM000295')) l
on trim(l."HPCode" )=trim(m.hpcode)
left join l2.pd_npi p1 on trim(c.npi)=trim(p1.npi)
left join (select * from l2.pd_ontology where dtp ='procedure' )ont
on trim(ont.cval) =trim(c.svccode )
--left join l2.pd_ontology ontd
--on trim(ontd.cval) =trim(c.pd) and ontd.dtp ='diagnosis'
left join l2.pd_npi p2 on trim(c.ref_npi )=trim(p2.npi)
)f

I ran explain command onto this query and I observed the following op:

  Merge Cond: ((btrim((p2.npi)::text)) = (btrim((c.ref_npi)::text)))
  ->  Sort  (cost=1761851.74..1785989.13 rows=9654958 width=108)
        Sort Key: (btrim((p2.npi)::text))
        ->  Seq Scan on pd_npi p2  (cost=0.00..641739.58 rows=9654958 width=108)
  ->  Materialize  (cost=159139479153552696279040.00..159226719441637676679168.00 rows=17448057616994486190080 width=3383)
        ->  Sort  (cost=159139479153552696279040.00..159183099297595186479104.00 rows=17448057616994486190080 width=3383)
              Sort Key: (btrim((c.ref_npi)::text))
              ->  Merge Join  (cost=456947875487.57..261731712177051860992.00 rows=17448057616994486190080 width=3383)
                    Merge Cond: ((c.claim_memb)::text = (dm_proc_memb.memb_id)::text)
                    ->  Nested Loop Left Join  (cost=157761.50..10839427546273626.00 rows=2707460197662070 width=2450)
                          Join Filter: (btrim((c.npi)::text) = btrim((p1.npi)::text))
                          ->  Nested Loop Left Join  (cost=157761.50..9586754880584.63 rows=56084349568 width=2353)
                                Join Filter: (btrim((pd_ontology.cval)::text) = btrim((c.svccode)::text))
                                ->  Nested Loop Left Join  (cost=0.56..9362416722695.26 rows=43719408 width=2348)
                                      Join Filter: ((mf.claimsvc_id)::text = (c.claimsvc_id)::text)
                                      ->  Index Scan Backward using l1_dm_proc_claims_p_id_idx on dm_proc_claim_p c  (cost=0.56..17080655.54 rows=43719408 width=2036)
                                      ->  Materialize  (cost=0.00..376215.44 rows=14276496 width=352)
                                            ->  Seq Scan on dm_proc_modif mf  (cost=0.00..304832.96 rows=14276496 width=352)
                                ->  Materialize  (cost=157760.93..760260.39 rows=256565 width=14)
                                      ->  Bitmap Heap Scan on pd_ontology  (cost=157760.93..758977.56 rows=256565 width=14)
                                            Recheck Cond: ((dtp)::text = 'procedure'::text)
                                            ->  Bitmap Index Scan on pd_ont_idx_dtp  (cost=0.00..157696.79 rows=256565 width=0)
                                                  Index Cond: ((dtp)::text = 'procedure'::text)
                          ->  Materialize  (cost=0.00..690014.37 rows=9654958 width=108)
                                ->  Seq Scan on pd_npi p1  (cost=0.00..641739.58 rows=9654958 width=108)
                    ->  Materialize  (cost=456947717726.07..459097227591.84 rows=429901973154 width=972)
                          ->  Sort  (cost=456947717726.07..458022472658.96 rows=429901973154 width=972)
                                Sort Key: dm_proc_memb.memb_id NULLS FIRST
                                ->  Merge Right Join  (cost=82668031.11..6531565545.79 rows=429901973154 width=972)
                                      Merge Cond: ((v.vend_id)::text = (dm_proc_memb.vend_id)::text)
                                      ->  Sort  (cost=88160.95..89633.33 rows=588951 width=96)
                                            Sort Key: v.vend_id
                                            ->  Seq Scan on dm_vend v  (cost=0.00..31716.51 rows=588951 width=96)
                                      ->  Materialize  (cost=82579870.16..83309815.39 rows=145989046 width=940)
                                            ->  Sort  (cost=82579870.16..82944842.77 rows=145989046 width=940)
                                                  Sort Key: dm_proc_memb.vend_id
                                                  ->  Merge Right Join  (cost=165139.84..2405511.13 rows=145989046 width=940)
                                                        Merge Cond: ((p.prov_id)::text = (dm_proc_memb.prov_id)::text)
                                                        ->  Index Scan using dm_prov_prov_id on dm_prov p  (cost=0.42..49937.79 rows=239291 width=160)
                                                        ->  Sort  (cost=165139.42..165444.47 rows=122018 width=844)
                                                              Sort Key: dm_proc_memb.prov_id
                                                              ->  Hash Left Join  (cost=485.00..154830.89 rows=122018 width=844)
                                                                    Hash Cond: (btrim((dm_proc_memb.hpcode)::text) = btrim((level."HPCode")::text))
                                                                    ->  Bitmap Heap Scan on dm_proc_memb  (cost=481.71..153985.68 rows=122018 width=812)
                                                                          Recheck Cond: ((hpcode)::text = ANY ('{PGM000333,PGM000332,PGM000326,PGM000268,PGM000269,PGM000270,PGM000272,PGM000273,PGM000274,PGM000275,PGM000276,PGM000294,PGM000295}'::text[]))
                                                                          ->  Bitmap Index Scan on l1_proc_memb_id_idx  (cost=0.00..451.20 rows=122018 width=0)
                                                                    ->  Hash  (cost=3.13..3.13 rows=13 width=64)
                                                                          ->  Seq Scan on level  (cost=0.00..3.13 rows=13 width=64)
                                                                                Filter: (("HPCode")::text = ANY ('{PGM000333,PGM000332,PGM000326,PGM000268,PGM000269,PGM000270,PGM000272,PGM000273,PGM000274,PGM000275,PGM000276,PGM000294,PGM000295}'::text[]))

The cost taken by the query is too much. How can I reduce the cost and save time.
P.S I analyzed and found that left joins are increasing the cost. Please tell me how to manage this.

The max size of the tables is 32 GB.

Thanks!!

Body:
That's a big query, possibly too big for StackOverflow volunteers to wrap our heads around. I do notice one thing, though: you have multiple occurrences of

left join t on trim(t.col) = trim(a.col)   /* slow! */

That's a query-performance antipattern. If you want good performance you should go to great lengths to avoid calling functions on column values in WHERE and ON clauses. You want all columns in those clauses to be sargable. You can call functions on constant values, though.

Your objective should be to have that LEFT JOIN read:

left join t on t.col = a.col

And you should be sure the two sides of ON clauses have precisely the same data type; that makes it easier for the database server to do the joins efficiently.

Your monster query seems to have a presentation layer with a long SELECT clause with many complex column definitions. Then, in its last 15 lines or so, it has a data-retrieval subquery starting with (select..... If you're trying to optimize the query, you'll be wise to focus on the data-retrieval subquery, stripping away the presentation layer.

Once you make your ON and WHERE clauses sargable, you can move on to looking at your tables' indexes.


来源: stackexchange

问:
Colab: (0) UNIMPLEMENTED: DNN library is not found
I have pretrained model for object detection (Google Colab + TensorFlow) inside Google Colab and I run it two-three times per week for new images I have and everything was fine for the last year till this week. Now when I try to run model I have this message:

```<code>Graph execution error:

2 root error(s) found.
  (0) UNIMPLEMENTED:  DNN library is not found.
     [[{{node functional_1/conv1_conv/Conv2D}}]]
     [[StatefulPartitionedCall/SecondStagePostprocessor/BatchMultiClassNonMaxSuppression/MultiClassNonMaxSuppression/Reshape_5/_126]]
  (1) UNIMPLEMENTED:  DNN library is not found.
     [[{{node functional_1/conv1_conv/Conv2D}}]]
0 successful operations.
0 derived errors ignored. [Op:__inference_restored_function_body_27380] ***
</code>```

Never happended before.

Before I can run my model I have to install Tensor Flow object detection API with this command:

```<code>import os

os.chdir('/project/models/research')

!protoc object_detection/protos/*.proto --python_out=.
!cp object_detection/packages/tf2/setup.py .
!python -m pip install .
</code>```

This is the output of command:

```Processing /content/gdrive/MyDrive/models/research
  DEPRECATION: A future pip version will change local packages to be built in-place without first copying to a temporary directory. We recommend you use --use-feature=in-tree-build to test your packages with this new behavior before it becomes the default.
   pip 21.3 will remove support for this functionality. You can find discussion regarding this at https://github.com/pypa/pip/issues/7555.
Collecting avro-python3
  Downloading avro-python3-1.10.2.tar.gz (38 kB)
Collecting apache-beam
  Downloading apache_beam-2.35.0-cp37-cp37m-manylinux2010_x86_64.whl (9.9 MB)
     |████████████████████████████████| 9.9 MB 1.6 MB/s
Requirement already satisfied: pillow in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (7.1.2)
Requirement already satisfied: lxml in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (4.2.6)
Requirement already satisfied: matplotlib in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (3.2.2)
Requirement already satisfied: Cython in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (0.29.27)
Requirement already satisfied: contextlib2 in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (0.5.5)
Collecting tf-slim
  Downloading tf_slim-1.1.0-py2.py3-none-any.whl (352 kB)
     |████████████████████████████████| 352 kB 50.5 MB/s
Requirement already satisfied: six in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (1.15.0)
Requirement already satisfied: pycocotools in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (2.0.4)
Collecting lvis
  Downloading lvis-0.5.3-py3-none-any.whl (14 kB)
Requirement already satisfied: scipy in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (1.4.1)
Requirement already satisfied: pandas in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (1.3.5)
Collecting tf-models-official>=2.5.1
  Downloading tf_models_official-2.8.0-py2.py3-none-any.whl (2.2 MB)
     |████████████████████████████████| 2.2 MB 38.3 MB/s
Collecting tensorflow_io
  Downloading tensorflow_io-0.24.0-cp37-cp37m-manylinux_2_12_x86_64.manylinux2010_x86_64.whl (23.4 MB)
     |████████████████████████████████| 23.4 MB 1.7 MB/s
Requirement already satisfied: keras in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (2.7.0)
Collecting opencv-python-headless
  Downloading opencv_python_headless-4.5.5.62-cp36-abi3-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (47.7 MB)
     |████████████████████████████████| 47.7 MB 74 kB/s
Collecting sacrebleu
  Downloading sacrebleu-2.0.0-py3-none-any.whl (90 kB)
     |████████████████████████████████| 90 kB 10.4 MB/s
Requirement already satisfied: kaggle>=1.3.9 in /usr/local/lib/python3.7/dist-packages (from tf-models-official>=2.5.1->object-detection==0.1) (1.5.12)
Requirement already satisfied: psutil>=5.4.3 in /usr/local/lib/python3.7/dist-packages (from tf-models-official>=2.5.1->object-detection==0.1) (5.4.8)
Requirement already satisfied: oauth2client in /usr/local/lib/python3.7/dist-packages (from tf-models-official>=2.5.1->object-detection==0.1) (4.1.3)
Collecting tensorflow-addons
  Downloading tensorflow_addons-0.15.0-cp37-cp37m-manylinux_2_12_x86_64.manylinux2010_x86_64.whl (1.1 MB)
     |████████████████████████████████| 1.1 MB 37.8 MB/s
Requirement already satisfied: gin-config in /usr/local/lib/python3.7/dist-packages (from tf-models-official>=2.5.1->object-detection==0.1) (0.5.0)
Requirement already satisfied: tensorflow-datasets in /usr/local/lib/python3.7/dist-packages (from tf-models-official>=2.5.1->object-detection==0.1) (4.0.1)
Collecting sentencepiece
  Downloading sentencepiece-0.1.96-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (1.2 MB)
     |████████████████████████████████| 1.2 MB 37.5 MB/s
Collecting tensorflow-model-optimization>=0.4.1
  Downloading tensorflow_model_optimization-0.7.0-py2.py3-none-any.whl (213 kB)
     |████████████████████████████████| 213 kB 42.7 MB/s
Collecting pyyaml<6.0,>=5.1
  Downloading PyYAML-5.4.1-cp37-cp37m-manylinux1_x86_64.whl (636 kB)
     |████████████████████████████████| 636 kB 53.3 MB/s
Collecting tensorflow-text~=2.8.0
  Downloading tensorflow_text-2.8.1-cp37-cp37m-manylinux_2_12_x86_64.manylinux2010_x86_64.whl (4.9 MB)
     |████████████████████████████████| 4.9 MB 46.1 MB/s
Requirement already satisfied: google-api-python-client>=1.6.7 in /usr/local/lib/python3.7/dist-packages (from tf-models-official>=2.5.1->object-detection==0.1) (1.12.10)
Requirement already satisfied: numpy>=1.15.4 in /usr/local/lib/python3.7/dist-packages (from tf-models-official>=2.5.1->object-detection==0.1) (1.19.5)
Requirement already satisfied: tensorflow-hub>=0.6.0 in /usr/local/lib/python3.7/dist-packages (from tf-models-official>=2.5.1->object-detection==0.1) (0.12.0)
Collecting seqeval
  Downloading seqeval-1.2.2.tar.gz (43 kB)
     |████████████████████████████████| 43 kB 2.1 MB/s
Collecting tensorflow~=2.8.0
  Downloading tensorflow-2.8.0-cp37-cp37m-manylinux2010_x86_64.whl (497.5 MB)
     |████████████████████████████████| 497.5 MB 28 kB/s
Collecting py-cpuinfo>=3.3.0
  Downloading py-cpuinfo-8.0.0.tar.gz (99 kB)
     |████████████████████████████████| 99 kB 10.1 MB/s
Requirement already satisfied: google-auth<3dev,>=1.16.0 in /usr/local/lib/python3.7/dist-packages (from google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (1.35.0)
Requirement already satisfied: uritemplate<4dev,>=3.0.0 in /usr/local/lib/python3.7/dist-packages (from google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (3.0.1)
Requirement already satisfied: httplib2<1dev,>=0.15.0 in /usr/local/lib/python3.7/dist-packages (from google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (0.17.4)
Requirement already satisfied: google-auth-httplib2>=0.0.3 in /usr/local/lib/python3.7/dist-packages (from google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (0.0.4)
Requirement already satisfied: google-api-core<3dev,>=1.21.0 in /usr/local/lib/python3.7/dist-packages (from google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (1.26.3)
Requirement already satisfied: setuptools>=40.3.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (57.4.0)
Requirement already satisfied: pytz in /usr/local/lib/python3.7/dist-packages (from google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (2018.9)
Requirement already satisfied: googleapis-common-protos<2.0dev,>=1.6.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (1.54.0)
Requirement already satisfied: requests<3.0.0dev,>=2.18.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (2.23.0)
Requirement already satisfied: packaging>=14.3 in /usr/local/lib/python3.7/dist-packages (from google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (21.3)
Requirement already satisfied: protobuf>=3.12.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (3.17.3)
Requirement already satisfied: pyasn1-modules>=0.2.1 in /usr/local/lib/python3.7/dist-packages (from google-auth<3dev,>=1.16.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (0.2.8)
Requirement already satisfied: rsa<5,>=3.1.4 in /usr/local/lib/python3.7/dist-packages (from google-auth<3dev,>=1.16.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (4.8)
Requirement already satisfied: cachetools<5.0,>=2.0.0 in /usr/local/lib/python3.7/dist-packages (from google-auth<3dev,>=1.16.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (4.2.4)
Requirement already satisfied: certifi in /usr/local/lib/python3.7/dist-packages (from kaggle>=1.3.9->tf-models-official>=2.5.1->object-detection==0.1) (2021.10.8)
Requirement already satisfied: urllib3 in /usr/local/lib/python3.7/dist-packages (from kaggle>=1.3.9->tf-models-official>=2.5.1->object-detection==0.1) (1.24.3)
Requirement already satisfied: python-dateutil in /usr/local/lib/python3.7/dist-packages (from kaggle>=1.3.9->tf-models-official>=2.5.1->object-detection==0.1) (2.8.2)
Requirement already satisfied: tqdm in /usr/local/lib/python3.7/dist-packages (from kaggle>=1.3.9->tf-models-official>=2.5.1->object-detection==0.1) (4.62.3)
Requirement already satisfied: python-slugify in /usr/local/lib/python3.7/dist-packages (from kaggle>=1.3.9->tf-models-official>=2.5.1->object-detection==0.1) (5.0.2)
Requirement already satisfied: pyparsing!=3.0.5,>=2.0.2 in /usr/local/lib/python3.7/dist-packages (from packaging>=14.3->google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (3.0.7)
Requirement already satisfied: pyasn1<0.5.0,>=0.4.6 in /usr/local/lib/python3.7/dist-packages (from pyasn1-modules>=0.2.1->google-auth<3dev,>=1.16.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (0.4.8)
Requirement already satisfied: idna<3,>=2.5 in /usr/local/lib/python3.7/dist-packages (from requests<3.0.0dev,>=2.18.0->google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (2.10)
Requirement already satisfied: chardet<4,>=3.0.2 in /usr/local/lib/python3.7/dist-packages (from requests<3.0.0dev,>=2.18.0->google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (3.0.4)
Requirement already satisfied: termcolor>=1.1.0 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.1.0)
Requirement already satisfied: libclang>=9.0.1 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (13.0.0)
Requirement already satisfied: h5py>=2.9.0 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (3.1.0)
Requirement already satisfied: astunparse>=1.6.0 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.6.3)
Requirement already satisfied: gast>=0.2.1 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (0.4.0)
Requirement already satisfied: google-pasta>=0.1.1 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (0.2.0)
Requirement already satisfied: typing-extensions>=3.6.6 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (3.10.0.2)
Requirement already satisfied: wrapt>=1.11.0 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.13.3)
Requirement already satisfied: tensorflow-io-gcs-filesystem>=0.23.1 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (0.23.1)
Collecting tf-estimator-nightly==2.8.0.dev2021122109
  Downloading tf_estimator_nightly-2.8.0.dev2021122109-py2.py3-none-any.whl (462 kB)
     |████████████████████████████████| 462 kB 49.5 MB/s
Requirement already satisfied: keras-preprocessing>=1.1.1 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.1.2)
Collecting tensorboard<2.9,>=2.8
  Downloading tensorboard-2.8.0-py3-none-any.whl (5.8 MB)
     |████████████████████████████████| 5.8 MB 41.2 MB/s
Requirement already satisfied: flatbuffers>=1.12 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (2.0)
Collecting keras
  Downloading keras-2.8.0-py2.py3-none-any.whl (1.4 MB)
     |████████████████████████████████| 1.4 MB 41.2 MB/s
Requirement already satisfied: opt-einsum>=2.3.2 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (3.3.0)
Collecting numpy>=1.15.4
  Downloading numpy-1.21.5-cp37-cp37m-manylinux_2_12_x86_64.manylinux2010_x86_64.whl (15.7 MB)
     |████████████████████████████████| 15.7 MB 41.4 MB/s
Requirement already satisfied: absl-py>=0.4.0 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.0.0)
Requirement already satisfied: grpcio<2.0,>=1.24.3 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.43.0)
Requirement already satisfied: wheel<1.0,>=0.23.0 in /usr/local/lib/python3.7/dist-packages (from astunparse>=1.6.0->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (0.37.1)
Requirement already satisfied: cached-property in /usr/local/lib/python3.7/dist-packages (from h5py>=2.9.0->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.5.2)
Requirement already satisfied: tensorboard-data-server<0.7.0,>=0.6.0 in /usr/local/lib/python3.7/dist-packages (from tensorboard<2.9,>=2.8->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (0.6.1)
Requirement already satisfied: werkzeug>=0.11.15 in /usr/local/lib/python3.7/dist-packages (from tensorboard<2.9,>=2.8->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.0.1)
Requirement already satisfied: google-auth-oauthlib<0.5,>=0.4.1 in /usr/local/lib/python3.7/dist-packages (from tensorboard<2.9,>=2.8->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (0.4.6)
Requirement already satisfied: tensorboard-plugin-wit>=1.6.0 in /usr/local/lib/python3.7/dist-packages (from tensorboard<2.9,>=2.8->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.8.1)
Requirement already satisfied: markdown>=2.6.8 in /usr/local/lib/python3.7/dist-packages (from tensorboard<2.9,>=2.8->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (3.3.6)
Requirement already satisfied: requests-oauthlib>=0.7.0 in /usr/local/lib/python3.7/dist-packages (from google-auth-oauthlib<0.5,>=0.4.1->tensorboard<2.9,>=2.8->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.3.1)
Requirement already satisfied: importlib-metadata>=4.4 in /usr/local/lib/python3.7/dist-packages (from markdown>=2.6.8->tensorboard<2.9,>=2.8->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (4.10.1)
Requirement already satisfied: zipp>=0.5 in /usr/local/lib/python3.7/dist-packages (from importlib-metadata>=4.4->markdown>=2.6.8->tensorboard<2.9,>=2.8->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (3.7.0)
Requirement already satisfied: oauthlib>=3.0.0 in /usr/local/lib/python3.7/dist-packages (from requests-oauthlib>=0.7.0->google-auth-oauthlib<0.5,>=0.4.1->tensorboard<2.9,>=2.8->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (3.2.0)
Requirement already satisfied: dm-tree~=0.1.1 in /usr/local/lib/python3.7/dist-packages (from tensorflow-model-optimization>=0.4.1->tf-models-official>=2.5.1->object-detection==0.1) (0.1.6)
Requirement already satisfied: crcmod<2.0,>=1.7 in /usr/local/lib/python3.7/dist-packages (from apache-beam->object-detection==0.1) (1.7)
Collecting fastavro<2,>=0.21.4
  Downloading fastavro-1.4.9-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (2.3 MB)
     |████████████████████████████████| 2.3 MB 38.1 MB/s
Requirement already satisfied: pyarrow<7.0.0,>=0.15.1 in /usr/local/lib/python3.7/dist-packages (from apache-beam->object-detection==0.1) (6.0.1)
Requirement already satisfied: pydot<2,>=1.2.0 in /usr/local/lib/python3.7/dist-packages (from apache-beam->object-detection==0.1) (1.3.0)
Collecting proto-plus<2,>=1.7.1
  Downloading proto_plus-1.19.9-py3-none-any.whl (45 kB)
     |████████████████████████████████| 45 kB 3.2 MB/s
Collecting requests<3.0.0dev,>=2.18.0
  Downloading requests-2.27.1-py2.py3-none-any.whl (63 kB)
     |████████████████████████████████| 63 kB 1.8 MB/s
Collecting dill<0.3.2,>=0.3.1.1
  Downloading dill-0.3.1.1.tar.gz (151 kB)
     |████████████████████████████████| 151 kB 44.4 MB/s
Collecting numpy>=1.15.4
  Downloading numpy-1.20.3-cp37-cp37m-manylinux_2_12_x86_64.manylinux2010_x86_64.whl (15.3 MB)
     |████████████████████████████████| 15.3 MB 21.1 MB/s
Collecting orjson<4.0
  Downloading orjson-3.6.6-cp37-cp37m-manylinux_2_24_x86_64.whl (245 kB)
     |████████████████████████████████| 245 kB 53.2 MB/s
Collecting hdfs<3.0.0,>=2.1.0
  Downloading hdfs-2.6.0-py3-none-any.whl (33 kB)
Collecting pymongo<4.0.0,>=3.8.0
  Downloading pymongo-3.12.3-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (508 kB)
     |████████████████████████████████| 508 kB 44.3 MB/s
Requirement already satisfied: docopt in /usr/local/lib/python3.7/dist-packages (from hdfs<3.0.0,>=2.1.0->apache-beam->object-detection==0.1) (0.6.2)
Collecting protobuf>=3.12.0
  Downloading protobuf-3.19.4-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (1.1 MB)
     |████████████████████████████████| 1.1 MB 47.3 MB/s
Requirement already satisfied: charset-normalizer~=2.0.0 in /usr/local/lib/python3.7/dist-packages (from requests<3.0.0dev,>=2.18.0->google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (2.0.11)
Requirement already satisfied: opencv-python>=4.1.0.25 in /usr/local/lib/python3.7/dist-packages (from lvis->object-detection==0.1) (4.1.2.30)
Requirement already satisfied: cycler>=0.10.0 in /usr/local/lib/python3.7/dist-packages (from lvis->object-detection==0.1) (0.11.0)
Requirement already satisfied: kiwisolver>=1.1.0 in /usr/local/lib/python3.7/dist-packages (from lvis->object-detection==0.1) (1.3.2)
Requirement already satisfied: text-unidecode>=1.3 in /usr/local/lib/python3.7/dist-packages (from python-slugify->kaggle>=1.3.9->tf-models-official>=2.5.1->object-detection==0.1) (1.3)
Requirement already satisfied: regex in /usr/local/lib/python3.7/dist-packages (from sacrebleu->tf-models-official>=2.5.1->object-detection==0.1) (2019.12.20)
Requirement already satisfied: tabulate>=0.8.9 in /usr/local/lib/python3.7/dist-packages (from sacrebleu->tf-models-official>=2.5.1->object-detection==0.1) (0.8.9)
Collecting portalocker
  Downloading portalocker-2.3.2-py2.py3-none-any.whl (15 kB)
Collecting colorama
  Downloading colorama-0.4.4-py2.py3-none-any.whl (16 kB)
Requirement already satisfied: scikit-learn>=0.21.3 in /usr/local/lib/python3.7/dist-packages (from seqeval->tf-models-official>=2.5.1->object-detection==0.1) (1.0.2)
Requirement already satisfied: joblib>=0.11 in /usr/local/lib/python3.7/dist-packages (from scikit-learn>=0.21.3->seqeval->tf-models-official>=2.5.1->object-detection==0.1) (1.1.0)
Requirement already satisfied: threadpoolctl>=2.0.0 in /usr/local/lib/python3.7/dist-packages (from scikit-learn>=0.21.3->seqeval->tf-models-official>=2.5.1->object-detection==0.1) (3.1.0)
Requirement already satisfied: typeguard>=2.7 in /usr/local/lib/python3.7/dist-packages (from tensorflow-addons->tf-models-official>=2.5.1->object-detection==0.1) (2.7.1)
Requirement already satisfied: promise in /usr/local/lib/python3.7/dist-packages (from tensorflow-datasets->tf-models-official>=2.5.1->object-detection==0.1) (2.3)
Requirement already satisfied: future in /usr/local/lib/python3.7/dist-packages (from tensorflow-datasets->tf-models-official>=2.5.1->object-detection==0.1) (0.16.0)
Requirement already satisfied: attrs>=18.1.0 in /usr/local/lib/python3.7/dist-packages (from tensorflow-datasets->tf-models-official>=2.5.1->object-detection==0.1) (21.4.0)
Requirement already satisfied: importlib-resources in /usr/local/lib/python3.7/dist-packages (from tensorflow-datasets->tf-models-official>=2.5.1->object-detection==0.1) (5.4.0)
Requirement already satisfied: tensorflow-metadata in /usr/local/lib/python3.7/dist-packages (from tensorflow-datasets->tf-models-official>=2.5.1->object-detection==0.1) (1.6.0)
Collecting tensorflow-io-gcs-filesystem>=0.23.1
  Downloading tensorflow_io_gcs_filesystem-0.24.0-cp37-cp37m-manylinux_2_12_x86_64.manylinux2010_x86_64.whl (2.1 MB)
     |████████████████████████████████| 2.1 MB 40.9 MB/s
Building wheels for collected packages: object-detection, py-cpuinfo, dill, avro-python3, seqeval
  Building wheel for object-detection (setup.py) ... done
  Created wheel for object-detection: filename=object_detection-0.1-py3-none-any.whl size=1686316 sha256=775b8c34c800b3b3139d1067abd686af9ce9158011fccfb5450ccfd9bf424a5a
  Stored in directory: /tmp/pip-ephem-wheel-cache-rmw0fvil/wheels/d0/e3/e9/b9ffe85019ec441e90d8ff9eddee9950c4c23b7598204390b9
  Building wheel for py-cpuinfo (setup.py) ... done
  Created wheel for py-cpuinfo: filename=py_cpuinfo-8.0.0-py3-none-any.whl size=22257 sha256=ac956c4c039868fdba78645bea056754e667e8840bea783ad2ca75e4d3e682c6
  Stored in directory: /root/.cache/pip/wheels/d2/f1/1f/041add21dc9c4220157f1bd2bd6afe1f1a49524c3396b94401
  Building wheel for dill (setup.py) ... done
  Created wheel for dill: filename=dill-0.3.1.1-py3-none-any.whl size=78544 sha256=d9c6cdfd69aea2b4d78e6afbbe2bc530394e4081eb186eb4f4cd02373ca739fd
  Stored in directory: /root/.cache/pip/wheels/a4/61/fd/c57e374e580aa78a45ed78d5859b3a44436af17e22ca53284f
  Building wheel for avro-python3 (setup.py) ... done
  Created wheel for avro-python3: filename=avro_python3-1.10.2-py3-none-any.whl size=44010 sha256=4eca8b4f30e4850d5dabccee36c40c8dda8a6c7e7058cfb7f0258eea5ce7b2b3
  Stored in directory: /root/.cache/pip/wheels/d6/e5/b1/6b151d9b535ee50aaa6ab27d145a0104b6df02e5636f0376da
  Building wheel for seqeval (setup.py) ... done
  Created wheel for seqeval: filename=seqeval-1.2.2-py3-none-any.whl size=16180 sha256=0ddfa46d0e36e9be346a90833ef11cc0d38cc7e744be34c5a0d321f997a30cba
  Stored in directory: /root/.cache/pip/wheels/05/96/ee/7cac4e74f3b19e3158dce26a20a1c86b3533c43ec72a549fd7
Successfully built object-detection py-cpuinfo dill avro-python3 seqeval
Installing collected packages: requests, protobuf, numpy, tf-estimator-nightly, tensorflow-io-gcs-filesystem, tensorboard, keras, tensorflow, portalocker, dill, colorama, tf-slim, tensorflow-text, tensorflow-model-optimization, tensorflow-addons, seqeval, sentencepiece, sacrebleu, pyyaml, pymongo, py-cpuinfo, proto-plus, orjson, opencv-python-headless, hdfs, fastavro, tf-models-official, tensorflow-io, lvis, avro-python3, apache-beam, object-detection
  Attempting uninstall: requests
    Found existing installation: requests 2.23.0
    Uninstalling requests-2.23.0:
      Successfully uninstalled requests-2.23.0
  Attempting uninstall: protobuf
    Found existing installation: protobuf 3.17.3
    Uninstalling protobuf-3.17.3:
      Successfully uninstalled protobuf-3.17.3
  Attempting uninstall: numpy
    Found existing installation: numpy 1.19.5
    Uninstalling numpy-1.19.5:
      Successfully uninstalled numpy-1.19.5
  Attempting uninstall: tensorflow-io-gcs-filesystem
    Found existing installation: tensorflow-io-gcs-filesystem 0.23.1
    Uninstalling tensorflow-io-gcs-filesystem-0.23.1:
      Successfully uninstalled tensorflow-io-gcs-filesystem-0.23.1
  Attempting uninstall: tensorboard
    Found existing installation: tensorboard 2.7.0
    Uninstalling tensorboard-2.7.0:
      Successfully uninstalled tensorboard-2.7.0
  Attempting uninstall: keras
    Found existing installation: keras 2.7.0
    Uninstalling keras-2.7.0:
      Successfully uninstalled keras-2.7.0
  Attempting uninstall: tensorflow
    Found existing installation: tensorflow 2.7.0
    Uninstalling tensorflow-2.7.0:
      Successfully uninstalled tensorflow-2.7.0
  Attempting uninstall: dill
    Found existing installation: dill 0.3.4
    Uninstalling dill-0.3.4:
      Successfully uninstalled dill-0.3.4
  Attempting uninstall: pyyaml
    Found existing installation: PyYAML 3.13
    Uninstalling PyYAML-3.13:
      Successfully uninstalled PyYAML-3.13
  Attempting uninstall: pymongo
    Found existing installation: pymongo 4.0.1
    Uninstalling pymongo-4.0.1:
      Successfully uninstalled pymongo-4.0.1
ERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.
yellowbrick 1.3.post1 requires numpy<1.20,>=1.16.0, but you have numpy 1.20.3 which is incompatible.
multiprocess 0.70.12.2 requires dill>=0.3.4, but you have dill 0.3.1.1 which is incompatible.
google-colab 1.0.0 requires requests~=2.23.0, but you have requests 2.27.1 which is incompatible.
datascience 0.10.6 requires folium==0.2.1, but you have folium 0.8.3 which is incompatible.
albumentations 0.1.12 requires imgaug<0.2.7,>=0.2.5, but you have imgaug 0.2.9 which is incompatible.
Successfully installed apache-beam-2.35.0 avro-python3-1.10.2 colorama-0.4.4 dill-0.3.1.1 fastavro-1.4.9 hdfs-2.6.0 keras-2.8.0 lvis-0.5.3 numpy-1.20.3 object-detection-0.1 opencv-python-headless-4.5.5.62 orjson-3.6.6 portalocker-2.3.2 proto-plus-1.19.9 protobuf-3.19.4 py-cpuinfo-8.0.0 pymongo-3.12.3 pyyaml-5.4.1 requests-2.27.1 sacrebleu-2.0.0 sentencepiece-0.1.96 seqeval-1.2.2 tensorboard-2.8.0 tensorflow-2.8.0 tensorflow-addons-0.15.0 tensorflow-io-0.24.0 tensorflow-io-gcs-filesystem-0.24.0 tensorflow-model-optimization-0.7.0 tensorflow-text-2.8.1 tf-estimator-nightly-2.8.0.dev2021122109 tf-models-official-2.8.0 tf-slim-1.1.0
```

I am noticing that this command uninstalling tensorflow 2.7 and installing tensorflow 2.8. I am not sure it was happening before. Maybe it's the reason DNN library link is missing o something?

I can confirm these:

Nothing was changed inside pretrained model or already installed model or object_detection source files I downloaded a year ago.

I tried to run command !pip install dnn - not working

I tried to restart runtime (without disconnecting) - not working

Somebody can help? Thanks.

答:
For people who have same error: after install object_detection just need to reinstall tensorflow=2.7.0 again by running this command:

```<code>!pip install tensorflow==2.7.0
</code>```

YOU NEED TO RESTART RUNTIME AFTER THAT (Menu -> Runtime -> Restart Runtime)

This will solve "(0) UNIMPLEMENTED: DNN library is not found" problem

ContentLicense: CC BY-SA 4.0

Body:
I have pretrained model for object detection (Google Colab + TensorFlow) inside Google Colab and I run it two-three times per week for new images I have and everything was fine for the last year till this week. Now when I try to run model I have this message:

Graph execution error:

2 root error(s) found.
  (0) UNIMPLEMENTED:  DNN library is not found.
     [[{{node functional_1/conv1_conv/Conv2D}}]]
     [[StatefulPartitionedCall/SecondStagePostprocessor/BatchMultiClassNonMaxSuppression/MultiClassNonMaxSuppression/Reshape_5/_126]]
  (1) UNIMPLEMENTED:  DNN library is not found.
     [[{{node functional_1/conv1_conv/Conv2D}}]]
0 successful operations.
0 derived errors ignored. [Op:__inference_restored_function_body_27380] ***

Never happended before.

Before I can run my model I have to install Tensor Flow object detection API with this command:

import os

os.chdir('/project/models/research')

!protoc object_detection/protos/*.proto --python_out=.
!cp object_detection/packages/tf2/setup.py .
!python -m pip install .

This is the output of command:

Processing /content/gdrive/MyDrive/models/research
  DEPRECATION: A future pip version will change local packages to be built in-place without first copying to a temporary directory. We recommend you use --use-feature=in-tree-build to test your packages with this new behavior before it becomes the default.
   pip 21.3 will remove support for this functionality. You can find discussion regarding this at https://github.com/pypa/pip/issues/7555.
Collecting avro-python3
  Downloading avro-python3-1.10.2.tar.gz (38 kB)
Collecting apache-beam
  Downloading apache_beam-2.35.0-cp37-cp37m-manylinux2010_x86_64.whl (9.9 MB)
     |████████████████████████████████| 9.9 MB 1.6 MB/s
Requirement already satisfied: pillow in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (7.1.2)
Requirement already satisfied: lxml in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (4.2.6)
Requirement already satisfied: matplotlib in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (3.2.2)
Requirement already satisfied: Cython in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (0.29.27)
Requirement already satisfied: contextlib2 in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (0.5.5)
Collecting tf-slim
  Downloading tf_slim-1.1.0-py2.py3-none-any.whl (352 kB)
     |████████████████████████████████| 352 kB 50.5 MB/s
Requirement already satisfied: six in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (1.15.0)
Requirement already satisfied: pycocotools in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (2.0.4)
Collecting lvis
  Downloading lvis-0.5.3-py3-none-any.whl (14 kB)
Requirement already satisfied: scipy in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (1.4.1)
Requirement already satisfied: pandas in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (1.3.5)
Collecting tf-models-official>=2.5.1
  Downloading tf_models_official-2.8.0-py2.py3-none-any.whl (2.2 MB)
     |████████████████████████████████| 2.2 MB 38.3 MB/s
Collecting tensorflow_io
  Downloading tensorflow_io-0.24.0-cp37-cp37m-manylinux_2_12_x86_64.manylinux2010_x86_64.whl (23.4 MB)
     |████████████████████████████████| 23.4 MB 1.7 MB/s
Requirement already satisfied: keras in /usr/local/lib/python3.7/dist-packages (from object-detection==0.1) (2.7.0)
Collecting opencv-python-headless
  Downloading opencv_python_headless-4.5.5.62-cp36-abi3-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (47.7 MB)
     |████████████████████████████████| 47.7 MB 74 kB/s
Collecting sacrebleu
  Downloading sacrebleu-2.0.0-py3-none-any.whl (90 kB)
     |████████████████████████████████| 90 kB 10.4 MB/s
Requirement already satisfied: kaggle>=1.3.9 in /usr/local/lib/python3.7/dist-packages (from tf-models-official>=2.5.1->object-detection==0.1) (1.5.12)
Requirement already satisfied: psutil>=5.4.3 in /usr/local/lib/python3.7/dist-packages (from tf-models-official>=2.5.1->object-detection==0.1) (5.4.8)
Requirement already satisfied: oauth2client in /usr/local/lib/python3.7/dist-packages (from tf-models-official>=2.5.1->object-detection==0.1) (4.1.3)
Collecting tensorflow-addons
  Downloading tensorflow_addons-0.15.0-cp37-cp37m-manylinux_2_12_x86_64.manylinux2010_x86_64.whl (1.1 MB)
     |████████████████████████████████| 1.1 MB 37.8 MB/s
Requirement already satisfied: gin-config in /usr/local/lib/python3.7/dist-packages (from tf-models-official>=2.5.1->object-detection==0.1) (0.5.0)
Requirement already satisfied: tensorflow-datasets in /usr/local/lib/python3.7/dist-packages (from tf-models-official>=2.5.1->object-detection==0.1) (4.0.1)
Collecting sentencepiece
  Downloading sentencepiece-0.1.96-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (1.2 MB)
     |████████████████████████████████| 1.2 MB 37.5 MB/s
Collecting tensorflow-model-optimization>=0.4.1
  Downloading tensorflow_model_optimization-0.7.0-py2.py3-none-any.whl (213 kB)
     |████████████████████████████████| 213 kB 42.7 MB/s
Collecting pyyaml<6.0,>=5.1
  Downloading PyYAML-5.4.1-cp37-cp37m-manylinux1_x86_64.whl (636 kB)
     |████████████████████████████████| 636 kB 53.3 MB/s
Collecting tensorflow-text~=2.8.0
  Downloading tensorflow_text-2.8.1-cp37-cp37m-manylinux_2_12_x86_64.manylinux2010_x86_64.whl (4.9 MB)
     |████████████████████████████████| 4.9 MB 46.1 MB/s
Requirement already satisfied: google-api-python-client>=1.6.7 in /usr/local/lib/python3.7/dist-packages (from tf-models-official>=2.5.1->object-detection==0.1) (1.12.10)
Requirement already satisfied: numpy>=1.15.4 in /usr/local/lib/python3.7/dist-packages (from tf-models-official>=2.5.1->object-detection==0.1) (1.19.5)
Requirement already satisfied: tensorflow-hub>=0.6.0 in /usr/local/lib/python3.7/dist-packages (from tf-models-official>=2.5.1->object-detection==0.1) (0.12.0)
Collecting seqeval
  Downloading seqeval-1.2.2.tar.gz (43 kB)
     |████████████████████████████████| 43 kB 2.1 MB/s
Collecting tensorflow~=2.8.0
  Downloading tensorflow-2.8.0-cp37-cp37m-manylinux2010_x86_64.whl (497.5 MB)
     |████████████████████████████████| 497.5 MB 28 kB/s
Collecting py-cpuinfo>=3.3.0
  Downloading py-cpuinfo-8.0.0.tar.gz (99 kB)
     |████████████████████████████████| 99 kB 10.1 MB/s
Requirement already satisfied: google-auth<3dev,>=1.16.0 in /usr/local/lib/python3.7/dist-packages (from google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (1.35.0)
Requirement already satisfied: uritemplate<4dev,>=3.0.0 in /usr/local/lib/python3.7/dist-packages (from google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (3.0.1)
Requirement already satisfied: httplib2<1dev,>=0.15.0 in /usr/local/lib/python3.7/dist-packages (from google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (0.17.4)
Requirement already satisfied: google-auth-httplib2>=0.0.3 in /usr/local/lib/python3.7/dist-packages (from google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (0.0.4)
Requirement already satisfied: google-api-core<3dev,>=1.21.0 in /usr/local/lib/python3.7/dist-packages (from google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (1.26.3)
Requirement already satisfied: setuptools>=40.3.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (57.4.0)
Requirement already satisfied: pytz in /usr/local/lib/python3.7/dist-packages (from google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (2018.9)
Requirement already satisfied: googleapis-common-protos<2.0dev,>=1.6.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (1.54.0)
Requirement already satisfied: requests<3.0.0dev,>=2.18.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (2.23.0)
Requirement already satisfied: packaging>=14.3 in /usr/local/lib/python3.7/dist-packages (from google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (21.3)
Requirement already satisfied: protobuf>=3.12.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (3.17.3)
Requirement already satisfied: pyasn1-modules>=0.2.1 in /usr/local/lib/python3.7/dist-packages (from google-auth<3dev,>=1.16.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (0.2.8)
Requirement already satisfied: rsa<5,>=3.1.4 in /usr/local/lib/python3.7/dist-packages (from google-auth<3dev,>=1.16.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (4.8)
Requirement already satisfied: cachetools<5.0,>=2.0.0 in /usr/local/lib/python3.7/dist-packages (from google-auth<3dev,>=1.16.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (4.2.4)
Requirement already satisfied: certifi in /usr/local/lib/python3.7/dist-packages (from kaggle>=1.3.9->tf-models-official>=2.5.1->object-detection==0.1) (2021.10.8)
Requirement already satisfied: urllib3 in /usr/local/lib/python3.7/dist-packages (from kaggle>=1.3.9->tf-models-official>=2.5.1->object-detection==0.1) (1.24.3)
Requirement already satisfied: python-dateutil in /usr/local/lib/python3.7/dist-packages (from kaggle>=1.3.9->tf-models-official>=2.5.1->object-detection==0.1) (2.8.2)
Requirement already satisfied: tqdm in /usr/local/lib/python3.7/dist-packages (from kaggle>=1.3.9->tf-models-official>=2.5.1->object-detection==0.1) (4.62.3)
Requirement already satisfied: python-slugify in /usr/local/lib/python3.7/dist-packages (from kaggle>=1.3.9->tf-models-official>=2.5.1->object-detection==0.1) (5.0.2)
Requirement already satisfied: pyparsing!=3.0.5,>=2.0.2 in /usr/local/lib/python3.7/dist-packages (from packaging>=14.3->google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (3.0.7)
Requirement already satisfied: pyasn1<0.5.0,>=0.4.6 in /usr/local/lib/python3.7/dist-packages (from pyasn1-modules>=0.2.1->google-auth<3dev,>=1.16.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (0.4.8)
Requirement already satisfied: idna<3,>=2.5 in /usr/local/lib/python3.7/dist-packages (from requests<3.0.0dev,>=2.18.0->google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (2.10)
Requirement already satisfied: chardet<4,>=3.0.2 in /usr/local/lib/python3.7/dist-packages (from requests<3.0.0dev,>=2.18.0->google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (3.0.4)
Requirement already satisfied: termcolor>=1.1.0 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.1.0)
Requirement already satisfied: libclang>=9.0.1 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (13.0.0)
Requirement already satisfied: h5py>=2.9.0 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (3.1.0)
Requirement already satisfied: astunparse>=1.6.0 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.6.3)
Requirement already satisfied: gast>=0.2.1 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (0.4.0)
Requirement already satisfied: google-pasta>=0.1.1 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (0.2.0)
Requirement already satisfied: typing-extensions>=3.6.6 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (3.10.0.2)
Requirement already satisfied: wrapt>=1.11.0 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.13.3)
Requirement already satisfied: tensorflow-io-gcs-filesystem>=0.23.1 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (0.23.1)
Collecting tf-estimator-nightly==2.8.0.dev2021122109
  Downloading tf_estimator_nightly-2.8.0.dev2021122109-py2.py3-none-any.whl (462 kB)
     |████████████████████████████████| 462 kB 49.5 MB/s
Requirement already satisfied: keras-preprocessing>=1.1.1 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.1.2)
Collecting tensorboard<2.9,>=2.8
  Downloading tensorboard-2.8.0-py3-none-any.whl (5.8 MB)
     |████████████████████████████████| 5.8 MB 41.2 MB/s
Requirement already satisfied: flatbuffers>=1.12 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (2.0)
Collecting keras
  Downloading keras-2.8.0-py2.py3-none-any.whl (1.4 MB)
     |████████████████████████████████| 1.4 MB 41.2 MB/s
Requirement already satisfied: opt-einsum>=2.3.2 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (3.3.0)
Collecting numpy>=1.15.4
  Downloading numpy-1.21.5-cp37-cp37m-manylinux_2_12_x86_64.manylinux2010_x86_64.whl (15.7 MB)
     |████████████████████████████████| 15.7 MB 41.4 MB/s
Requirement already satisfied: absl-py>=0.4.0 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.0.0)
Requirement already satisfied: grpcio<2.0,>=1.24.3 in /usr/local/lib/python3.7/dist-packages (from tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.43.0)
Requirement already satisfied: wheel<1.0,>=0.23.0 in /usr/local/lib/python3.7/dist-packages (from astunparse>=1.6.0->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (0.37.1)
Requirement already satisfied: cached-property in /usr/local/lib/python3.7/dist-packages (from h5py>=2.9.0->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.5.2)
Requirement already satisfied: tensorboard-data-server<0.7.0,>=0.6.0 in /usr/local/lib/python3.7/dist-packages (from tensorboard<2.9,>=2.8->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (0.6.1)
Requirement already satisfied: werkzeug>=0.11.15 in /usr/local/lib/python3.7/dist-packages (from tensorboard<2.9,>=2.8->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.0.1)
Requirement already satisfied: google-auth-oauthlib<0.5,>=0.4.1 in /usr/local/lib/python3.7/dist-packages (from tensorboard<2.9,>=2.8->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (0.4.6)
Requirement already satisfied: tensorboard-plugin-wit>=1.6.0 in /usr/local/lib/python3.7/dist-packages (from tensorboard<2.9,>=2.8->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.8.1)
Requirement already satisfied: markdown>=2.6.8 in /usr/local/lib/python3.7/dist-packages (from tensorboard<2.9,>=2.8->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (3.3.6)
Requirement already satisfied: requests-oauthlib>=0.7.0 in /usr/local/lib/python3.7/dist-packages (from google-auth-oauthlib<0.5,>=0.4.1->tensorboard<2.9,>=2.8->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (1.3.1)
Requirement already satisfied: importlib-metadata>=4.4 in /usr/local/lib/python3.7/dist-packages (from markdown>=2.6.8->tensorboard<2.9,>=2.8->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (4.10.1)
Requirement already satisfied: zipp>=0.5 in /usr/local/lib/python3.7/dist-packages (from importlib-metadata>=4.4->markdown>=2.6.8->tensorboard<2.9,>=2.8->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (3.7.0)
Requirement already satisfied: oauthlib>=3.0.0 in /usr/local/lib/python3.7/dist-packages (from requests-oauthlib>=0.7.0->google-auth-oauthlib<0.5,>=0.4.1->tensorboard<2.9,>=2.8->tensorflow~=2.8.0->tf-models-official>=2.5.1->object-detection==0.1) (3.2.0)
Requirement already satisfied: dm-tree~=0.1.1 in /usr/local/lib/python3.7/dist-packages (from tensorflow-model-optimization>=0.4.1->tf-models-official>=2.5.1->object-detection==0.1) (0.1.6)
Requirement already satisfied: crcmod<2.0,>=1.7 in /usr/local/lib/python3.7/dist-packages (from apache-beam->object-detection==0.1) (1.7)
Collecting fastavro<2,>=0.21.4
  Downloading fastavro-1.4.9-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (2.3 MB)
     |████████████████████████████████| 2.3 MB 38.1 MB/s
Requirement already satisfied: pyarrow<7.0.0,>=0.15.1 in /usr/local/lib/python3.7/dist-packages (from apache-beam->object-detection==0.1) (6.0.1)
Requirement already satisfied: pydot<2,>=1.2.0 in /usr/local/lib/python3.7/dist-packages (from apache-beam->object-detection==0.1) (1.3.0)
Collecting proto-plus<2,>=1.7.1
  Downloading proto_plus-1.19.9-py3-none-any.whl (45 kB)
     |████████████████████████████████| 45 kB 3.2 MB/s
Collecting requests<3.0.0dev,>=2.18.0
  Downloading requests-2.27.1-py2.py3-none-any.whl (63 kB)
     |████████████████████████████████| 63 kB 1.8 MB/s
Collecting dill<0.3.2,>=0.3.1.1
  Downloading dill-0.3.1.1.tar.gz (151 kB)
     |████████████████████████████████| 151 kB 44.4 MB/s
Collecting numpy>=1.15.4
  Downloading numpy-1.20.3-cp37-cp37m-manylinux_2_12_x86_64.manylinux2010_x86_64.whl (15.3 MB)
     |████████████████████████████████| 15.3 MB 21.1 MB/s
Collecting orjson<4.0
  Downloading orjson-3.6.6-cp37-cp37m-manylinux_2_24_x86_64.whl (245 kB)
     |████████████████████████████████| 245 kB 53.2 MB/s
Collecting hdfs<3.0.0,>=2.1.0
  Downloading hdfs-2.6.0-py3-none-any.whl (33 kB)
Collecting pymongo<4.0.0,>=3.8.0
  Downloading pymongo-3.12.3-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (508 kB)
     |████████████████████████████████| 508 kB 44.3 MB/s
Requirement already satisfied: docopt in /usr/local/lib/python3.7/dist-packages (from hdfs<3.0.0,>=2.1.0->apache-beam->object-detection==0.1) (0.6.2)
Collecting protobuf>=3.12.0
  Downloading protobuf-3.19.4-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (1.1 MB)
     |████████████████████████████████| 1.1 MB 47.3 MB/s
Requirement already satisfied: charset-normalizer~=2.0.0 in /usr/local/lib/python3.7/dist-packages (from requests<3.0.0dev,>=2.18.0->google-api-core<3dev,>=1.21.0->google-api-python-client>=1.6.7->tf-models-official>=2.5.1->object-detection==0.1) (2.0.11)
Requirement already satisfied: opencv-python>=4.1.0.25 in /usr/local/lib/python3.7/dist-packages (from lvis->object-detection==0.1) (4.1.2.30)
Requirement already satisfied: cycler>=0.10.0 in /usr/local/lib/python3.7/dist-packages (from lvis->object-detection==0.1) (0.11.0)
Requirement already satisfied: kiwisolver>=1.1.0 in /usr/local/lib/python3.7/dist-packages (from lvis->object-detection==0.1) (1.3.2)
Requirement already satisfied: text-unidecode>=1.3 in /usr/local/lib/python3.7/dist-packages (from python-slugify->kaggle>=1.3.9->tf-models-official>=2.5.1->object-detection==0.1) (1.3)
Requirement already satisfied: regex in /usr/local/lib/python3.7/dist-packages (from sacrebleu->tf-models-official>=2.5.1->object-detection==0.1) (2019.12.20)
Requirement already satisfied: tabulate>=0.8.9 in /usr/local/lib/python3.7/dist-packages (from sacrebleu->tf-models-official>=2.5.1->object-detection==0.1) (0.8.9)
Collecting portalocker
  Downloading portalocker-2.3.2-py2.py3-none-any.whl (15 kB)
Collecting colorama
  Downloading colorama-0.4.4-py2.py3-none-any.whl (16 kB)
Requirement already satisfied: scikit-learn>=0.21.3 in /usr/local/lib/python3.7/dist-packages (from seqeval->tf-models-official>=2.5.1->object-detection==0.1) (1.0.2)
Requirement already satisfied: joblib>=0.11 in /usr/local/lib/python3.7/dist-packages (from scikit-learn>=0.21.3->seqeval->tf-models-official>=2.5.1->object-detection==0.1) (1.1.0)
Requirement already satisfied: threadpoolctl>=2.0.0 in /usr/local/lib/python3.7/dist-packages (from scikit-learn>=0.21.3->seqeval->tf-models-official>=2.5.1->object-detection==0.1) (3.1.0)
Requirement already satisfied: typeguard>=2.7 in /usr/local/lib/python3.7/dist-packages (from tensorflow-addons->tf-models-official>=2.5.1->object-detection==0.1) (2.7.1)
Requirement already satisfied: promise in /usr/local/lib/python3.7/dist-packages (from tensorflow-datasets->tf-models-official>=2.5.1->object-detection==0.1) (2.3)
Requirement already satisfied: future in /usr/local/lib/python3.7/dist-packages (from tensorflow-datasets->tf-models-official>=2.5.1->object-detection==0.1) (0.16.0)
Requirement already satisfied: attrs>=18.1.0 in /usr/local/lib/python3.7/dist-packages (from tensorflow-datasets->tf-models-official>=2.5.1->object-detection==0.1) (21.4.0)
Requirement already satisfied: importlib-resources in /usr/local/lib/python3.7/dist-packages (from tensorflow-datasets->tf-models-official>=2.5.1->object-detection==0.1) (5.4.0)
Requirement already satisfied: tensorflow-metadata in /usr/local/lib/python3.7/dist-packages (from tensorflow-datasets->tf-models-official>=2.5.1->object-detection==0.1) (1.6.0)
Collecting tensorflow-io-gcs-filesystem>=0.23.1
  Downloading tensorflow_io_gcs_filesystem-0.24.0-cp37-cp37m-manylinux_2_12_x86_64.manylinux2010_x86_64.whl (2.1 MB)
     |████████████████████████████████| 2.1 MB 40.9 MB/s
Building wheels for collected packages: object-detection, py-cpuinfo, dill, avro-python3, seqeval
  Building wheel for object-detection (setup.py) ... done
  Created wheel for object-detection: filename=object_detection-0.1-py3-none-any.whl size=1686316 sha256=775b8c34c800b3b3139d1067abd686af9ce9158011fccfb5450ccfd9bf424a5a
  Stored in directory: /tmp/pip-ephem-wheel-cache-rmw0fvil/wheels/d0/e3/e9/b9ffe85019ec441e90d8ff9eddee9950c4c23b7598204390b9
  Building wheel for py-cpuinfo (setup.py) ... done
  Created wheel for py-cpuinfo: filename=py_cpuinfo-8.0.0-py3-none-any.whl size=22257 sha256=ac956c4c039868fdba78645bea056754e667e8840bea783ad2ca75e4d3e682c6
  Stored in directory: /root/.cache/pip/wheels/d2/f1/1f/041add21dc9c4220157f1bd2bd6afe1f1a49524c3396b94401
  Building wheel for dill (setup.py) ... done
  Created wheel for dill: filename=dill-0.3.1.1-py3-none-any.whl size=78544 sha256=d9c6cdfd69aea2b4d78e6afbbe2bc530394e4081eb186eb4f4cd02373ca739fd
  Stored in directory: /root/.cache/pip/wheels/a4/61/fd/c57e374e580aa78a45ed78d5859b3a44436af17e22ca53284f
  Building wheel for avro-python3 (setup.py) ... done
  Created wheel for avro-python3: filename=avro_python3-1.10.2-py3-none-any.whl size=44010 sha256=4eca8b4f30e4850d5dabccee36c40c8dda8a6c7e7058cfb7f0258eea5ce7b2b3
  Stored in directory: /root/.cache/pip/wheels/d6/e5/b1/6b151d9b535ee50aaa6ab27d145a0104b6df02e5636f0376da
  Building wheel for seqeval (setup.py) ... done
  Created wheel for seqeval: filename=seqeval-1.2.2-py3-none-any.whl size=16180 sha256=0ddfa46d0e36e9be346a90833ef11cc0d38cc7e744be34c5a0d321f997a30cba
  Stored in directory: /root/.cache/pip/wheels/05/96/ee/7cac4e74f3b19e3158dce26a20a1c86b3533c43ec72a549fd7
Successfully built object-detection py-cpuinfo dill avro-python3 seqeval
Installing collected packages: requests, protobuf, numpy, tf-estimator-nightly, tensorflow-io-gcs-filesystem, tensorboard, keras, tensorflow, portalocker, dill, colorama, tf-slim, tensorflow-text, tensorflow-model-optimization, tensorflow-addons, seqeval, sentencepiece, sacrebleu, pyyaml, pymongo, py-cpuinfo, proto-plus, orjson, opencv-python-headless, hdfs, fastavro, tf-models-official, tensorflow-io, lvis, avro-python3, apache-beam, object-detection
  Attempting uninstall: requests
    Found existing installation: requests 2.23.0
    Uninstalling requests-2.23.0:
      Successfully uninstalled requests-2.23.0
  Attempting uninstall: protobuf
    Found existing installation: protobuf 3.17.3
    Uninstalling protobuf-3.17.3:
      Successfully uninstalled protobuf-3.17.3
  Attempting uninstall: numpy
    Found existing installation: numpy 1.19.5
    Uninstalling numpy-1.19.5:
      Successfully uninstalled numpy-1.19.5
  Attempting uninstall: tensorflow-io-gcs-filesystem
    Found existing installation: tensorflow-io-gcs-filesystem 0.23.1
    Uninstalling tensorflow-io-gcs-filesystem-0.23.1:
      Successfully uninstalled tensorflow-io-gcs-filesystem-0.23.1
  Attempting uninstall: tensorboard
    Found existing installation: tensorboard 2.7.0
    Uninstalling tensorboard-2.7.0:
      Successfully uninstalled tensorboard-2.7.0
  Attempting uninstall: keras
    Found existing installation: keras 2.7.0
    Uninstalling keras-2.7.0:
      Successfully uninstalled keras-2.7.0
  Attempting uninstall: tensorflow
    Found existing installation: tensorflow 2.7.0
    Uninstalling tensorflow-2.7.0:
      Successfully uninstalled tensorflow-2.7.0
  Attempting uninstall: dill
    Found existing installation: dill 0.3.4
    Uninstalling dill-0.3.4:
      Successfully uninstalled dill-0.3.4
  Attempting uninstall: pyyaml
    Found existing installation: PyYAML 3.13
    Uninstalling PyYAML-3.13:
      Successfully uninstalled PyYAML-3.13
  Attempting uninstall: pymongo
    Found existing installation: pymongo 4.0.1
    Uninstalling pymongo-4.0.1:
      Successfully uninstalled pymongo-4.0.1
ERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.
yellowbrick 1.3.post1 requires numpy<1.20,>=1.16.0, but you have numpy 1.20.3 which is incompatible.
multiprocess 0.70.12.2 requires dill>=0.3.4, but you have dill 0.3.1.1 which is incompatible.
google-colab 1.0.0 requires requests~=2.23.0, but you have requests 2.27.1 which is incompatible.
datascience 0.10.6 requires folium==0.2.1, but you have folium 0.8.3 which is incompatible.
albumentations 0.1.12 requires imgaug<0.2.7,>=0.2.5, but you have imgaug 0.2.9 which is incompatible.
Successfully installed apache-beam-2.35.0 avro-python3-1.10.2 colorama-0.4.4 dill-0.3.1.1 fastavro-1.4.9 hdfs-2.6.0 keras-2.8.0 lvis-0.5.3 numpy-1.20.3 object-detection-0.1 opencv-python-headless-4.5.5.62 orjson-3.6.6 portalocker-2.3.2 proto-plus-1.19.9 protobuf-3.19.4 py-cpuinfo-8.0.0 pymongo-3.12.3 pyyaml-5.4.1 requests-2.27.1 sacrebleu-2.0.0 sentencepiece-0.1.96 seqeval-1.2.2 tensorboard-2.8.0 tensorflow-2.8.0 tensorflow-addons-0.15.0 tensorflow-io-0.24.0 tensorflow-io-gcs-filesystem-0.24.0 tensorflow-model-optimization-0.7.0 tensorflow-text-2.8.1 tf-estimator-nightly-2.8.0.dev2021122109 tf-models-official-2.8.0 tf-slim-1.1.0

I am noticing that this command uninstalling tensorflow 2.7 and installing tensorflow 2.8. I am not sure it was happening before. Maybe it's the reason DNN library link is missing o something?

I can confirm these:

Nothing was changed inside pretrained model or already installed model or object_detection source files I downloaded a year ago.

I tried to run command !pip install dnn - not working

I tried to restart runtime (without disconnecting) - not working

Somebody can help? Thanks.

Body:
For people who have same error: after install object_detection just need to reinstall tensorflow=2.7.0 again by running this command:

!pip install tensorflow==2.7.0

YOU NEED TO RESTART RUNTIME AFTER THAT (Menu -> Runtime -> Restart Runtime)

This will solve "(0) UNIMPLEMENTED: DNN library is not found" problem